sql; table relationships

I have a table called all :
All table:

Al_id(primary key)
P_cd (foreign key relationship to table called prom) ; can be null

Prom table:
P_cd (primary key)

Now when I do a :
select * from  all
where p_cd is null
I get some records where the p_code is null. Does that not mean that the foreign key relationship is violated? There are currently no null values in the prom table& Please clarify if there is some problem with this table&
Sara_j_11Asked:
Who is Participating?
 
Mark GeerlingsDatabase AdministratorCommented:
A null value in a foreign key column does not violate Oracle's understanding (implementation) of a foreign key relationship.
0
 
johnsoneSenior Oracle DBACommented:
According to Oracle's definitions, no there are no problems.

If you want to enforce that there must be a parent record in the PROM table for every record in the ALL table, you need to make the P_CD column NOT NULL.
0
 
MikeOM_DBACommented:
Whats the problem?
You designed All table to allow P_cd (foreign key relationship to table called prom) to be null.

0
 
bibhu_datta_routCommented:
Hi, you have asked 3 questions
1.if foreign key is null, then its not violation of any rules as Foreign keys can be NULL accroding to oracle.
2.There must be values in the prom which are null.how are u testing it.
   select count(*) from prom where p_cd is not NULL.
  Are all PKs are valid. Please check the same.
3.And for resolution of ur problem fK should be NOT NULL.

Get back to us, in case answer not resolved.
Bibhu
0
 
Mark GeerlingsDatabase AdministratorCommented:
No, the data in the question *DOES NOT* imply that there are records in the "prom" table with a null value.  It only tells us there are records in the "all" table with a null value in the column that is a foreign key to the "prom" table.  And, that is OK according to Oracle's documentation of how foreign keys work in a column where null values are allowed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.