Foreign Keys

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.

Correction June 2020: The highlighted statement is wrong. If you check the truth tables for three-valued logic (3VL) you will find that:

“FALSE and UNKNOWN” evaluates to FALSE, not to UNKNOWN

The foreign key behaviour can be explained only by stating that Oracle Corp. chose to implement from “match none” from the ANSI standard which allows “match none”, “match partial”, “match full”. (See end-note)

Thanks to mathguy in this Oracle Developer Forum thread for pointing out my error.

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.

Endnote (June 2020)

Here’s a link to the 11.2 App Dev manual (and one to the 19c manual) where it describes the handling of foreign key constraints and nulls, and mentions the ANSI standards of “match none”, “match partial” and “match full”.

You can probably find the description of these three options somewhere on line, but I have their descriptions noted down from the SQL-92 standard as:

• Match Full: Partially null foreign keys are not permitted. Either all components of the foreign key must be null, or the combination of values contained in the foreign key must appear as the primary or unique key value of a single row of the referenced table. [Default]
• Match Partial: Partially null composite foreign keys are permitted. Either all components of the foreign key must be null, or the combination of non-null values contained in the foreign key must appear in the corresponding portion of the primary or unique key value of a single row in the referenced table.
• Match None: Partially null composite foreign keys are permitted. If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key.

Note that completely null composite foreign keys are always allowed – assuming, of course, that none of the individual columns has been declared not null.