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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
A null value in a foreign key column does not violate Oracle's understanding (implementation) of a foreign key relationship.
0
 
johnsoneConnect With a Mentor Senior 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_DBAConnect With a Mentor Commented:
Whats the problem?
You designed All table to allow P_cd (foreign key relationship to table called prom) to be null.

0
 
bibhu_datta_routConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.