jcon.no: Oracle blog

I used to believe that implementing a foreign key in Oracle enforced referential integrity. Until today! Until reading this article by Tom Kyte, showing that this isn’t necessarily the truth. And I don’t think very many is aware of this. Yes – it does involve foreign keys allowing NULL values, but is’t that rather common?

Tom Kyte showed the following example (sorry for copying your example, but I leave the credit to you), using the following tables:

SQL> create table p
  2  	( x int,
  3  	  y int,
  4  	  z int,
  5  	  constraint p_pk primary key(x,y)
  6  	)
  7  /

Table created.

SQL> 
SQL> 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.

As Tom explains this is a rather normal parent child relation introducing referential integrity. Which says something like: “Referential integrity garantees that there won’t exist a foreign key value in the child table, that doesn’t also exist as a primary key value in the parent table”.

But look at the rest of Tom’s example:

SQL> insert into c values (1,null,0);

1 row created.
 
SQL> select count( x||y ) from p;

COUNT(X||Y)
-----------
	  0
 
SQL> select count( x||y ) from c;

COUNT(X||Y)
-----------
	  1

Therefore – when having a composite foreign key allowing NULL values, this is not enough to implement referential integrity (as defined in the relational theory).

Actually I was on a seminar this other week with Chris Date, which strongly believe that you should not allow NULLs in your database (design). This is one of the examples that actually support this theory.
You might not want to remove all NULLs in your database. But you might want to introduce a constraint to complete the referential integrity constraint (you really thought was there all the time). As Tom suggests in his article:

SQL> rollback;

Rollback complete.

SQL> 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 ) )
SQL> /

Table altered.

SQL> insert into c values (1,null,0);
insert into c values (1,null,0)
*
ERROR at line 1:
ORA-02290: check constraint (LJ.CHECK_NULLNESS) violated

Then the foreign key once again works as I originally thought they did. Thanks Tom!