There’s a posting on the OTN forum that raises one of the most common problems that people have with foreign keys – how they handle null.
Consider the following simple table definitions:
drop table child; drop table parent; create table parent( last_name varchar2(10), first_name varchar2(10) ); alter table parent add constraint par_uk unique(last_name, first_name); create table child( last_name_p varchar2(10), first_name_p varchar2(10) ); alter table child add constraint chi_fk_par foreign key(last_name_p, first_name_p) references parent(last_name, first_name) ;
Let’s insert a couple of rows of data to prove that the foreign key integrity check is working:
SQL> insert into parent values('Smith','John'); 1 row created. SQL> insert into child values('Smith','John'); 1 row created. SQL> insert into child values('Doe','Jane'); insert into child values('Doe','Jane') * ERROR at line 1: ORA-02291: integrity constraint (TEST_USER.CHI_FK_PAR) violated - parent key not found
As expected, we can insert child “John Smith”, but not “Jane Doe” – but how many of the following would you expect to be recognised as legitimate child rows:
SQL> insert into child values('Smith',null); SQL> insert into child values(null,'John'); SQL> insert into child values('Doe',null); SQL> insert into child values(null,'Jane'); SQL> insert into child values(null,null);
Every single one will pass the foreign key constraint check, despite the fact that the human eye can see quite clearly that the “appropriate match” doesn’t exist. This isn’t a bug – it’s Oracle obeying (one of) the ANSI standard rules for referential integrity.
One way to think about this is to work on the assumption that there is an important difference between predicates and constraints.
- A predicate accepts rows if it evaluates to TRUE.
- A constraint accepts rows if it does not evaluate to FALSE.
If you think the two descriptions are saying pretty much the same thing then you’re forgetting NULL. A boolean expression can evaluate to TRUE, FALSE or NULL in Oracle – and that can cause enormous confusion.
When Oracle checks the foreign key constraint, it’s basically evaluating the truth of:
parent.last_name = child.last_name_p and parent.first_name = child.first_name_p
If either of the child columns is null this expression returns NULL, which is not the same as FALSE - so the row passes the test of the constraint.
In fact, having chosen to follow this version of the ANSI standard, Oracle’s treatment of the check is not totally consistent. If it were then a null appearing at the parent end would also cause the check to evaluate to NULL and allow any child row to be accepted – but it doesn’t work like that. (If you were wondering why my example specified a unique constraint on the parent rather than a primary key it was so that I could check that last particular case).
As a general guideline – if you know of any condition of your data that can be expressed as a constraint then you should include it in the data definition. This is particularly important when you come to think about whether some data item is mandatory or not. If it has to be there then declare the column as NOT NULL.
Footnote: adding NOT NULL constraints to data may allow the optimizer to find extra (possibly more efficient) execution paths for some queries.