Wednesday, May 23, 2012

When is a foreign key not a foreign key...

I learn or relearn something new every day about Oracle.  Just about every day really!

Last week I was in Belgrade Serbia delivering a seminar and an attendee reminded me of something I knew once but had totally forgotten about.  It had to do with foreign keys and the dreaded NULL value.

Many of you might think the following to be not possible, we'll start with the tables:

ops$tkyte%ORA11GR2> create table p
  2  ( x int,
  3    y int,
  4    z int,
  5    constraint p_pk primary key(x,y)
  6  )
  7  /
Table created.

ops$tkyte%ORA11GR2> create table c
  2  ( x int,
  3    y int,
  4    z int,
  5    constraint c_fk_p foreign key (x,y) references p(x,y)
  6  )
  7  /
Table created.

Looks like a normal parent child relationship - a row may exist in C if and only if a parent row exists in P.  If that is true - then how can this happen:

ops$tkyte%ORA11GR2> select count( x||y ) from p;


ops$tkyte%ORA11GR2> select count( x||y ) from c;


There are zero records in P - none.  There is at least one record in C and that record has a non-null foreign key.  What is happening?

It has to do with NULLs and foreign keys and the default "MATCH NONE" rule in place.  If your foreign key allows NULLs and your foreign key is a composite key - then you must be careful of the condition where by only SOME of the foreign key attributes are not null.  For example - to achieve the above magic, I inserted:

ops$tkyte%ORA11GR2> insert into c values ( 1, null, 0 );
1 row created.

The database cannot validate a foreign key when it is partially null.  In order to enforce the "MATCH FULL" option of a foreign key - you would want to add a constraint to your table:

ops$tkyte%ORA11GR2> alter table c add constraint check_nullness
  2  check ( ( x is not null and y is not null ) or
  3          ( x is null and y is null ) )
  4  /
Table altered.

That will ensure either:
  • All of the columns are NULL in the foreign key
  • None of the columns are NULL in the foreign key
As long as that constraint is in place - your foreign key will work as  you probably think it should work.



Blogger Валерий Юринский said....

Very instructive example. Thank you!

Wed May 23, 06:23:00 PM EDT  

Blogger Валерий Юринский said....

Very instructive example. Thank you!

Wed May 23, 06:24:00 PM EDT  

Anonymous Anonymous said....

Tom thanks for sharing it.

Wed May 23, 09:28:00 PM EDT  

Anonymous Laurent Schneider said....

I did not know

Thu May 24, 01:46:00 AM EDT  

Blogger Bala said....

Very useful.

Thu May 24, 04:43:00 AM EDT  

Blogger Mohamed Houri said....

It's all about the difference between a predicate and a constraint

(a) predicate pass the condition when it evaluates to TRUE
(b) a constraint pass the condition if it does not evaluate to FALSE

So, a Foreign constraint can pass the condition when it evaluates to TRUE or NULL

Mohamed Houri

Thu May 24, 05:28:00 AM EDT  

Blogger Rafael Henchen said....

How awkward! That's because Oracle evaluates NULL as boolean "unknown", so they are evaluated as "not FALSE" and are accepted when inserted into the child table?
But in that way the referential integrity is not guaranteed, right?
Sounds like a bug to me, don't you think?
Anyway, thanks a lot for sharing Tom.

Fri May 25, 12:42:00 PM EDT  

Blogger Thomas Kyte said....

@Rafael -

sounds more like one of the ANSI standard options ;)

Not a bug, standard behavior and for good measure coupled with documentation (in more than one place) warning us of this behavior with a good way to prevent it (the check constraint)

Fri May 25, 12:55:00 PM EDT  

Blogger Sasa Petkovic said....

To be honest I have warned Tom (in fact reminded him) about this, and said that this is not just trick but "feature" covered by Oracle documentation. Again thanks to Tom for excellent seminar in Belgrade and his dedication to share knowledge with all of us.

Thu May 31, 02:16:00 AM EDT  

Anonymous Lasse Jenssen said....

I just attended a seminar on Database Design with Chris Date, and this is what I think he would say: "I told you! You shouldn't have those NULLs in your database in the first place!"

Wed Jun 20, 01:52:00 PM EDT  

Blogger Thomas Kyte said....


funny enough - I just presented recently in Finland. I followed Chris Date. He talked about not using nulls for an hour.

I talked about about some NULL use cases :(

Wed Jun 20, 01:54:00 PM EDT  


<< Home