Oracle Scratchpad

May 3, 2009

Foreign Keys

Filed under: Infrastructure,Troubleshooting — Jonathan Lewis @ 8:43 pm BST May 3,2009

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’s the single option that Oracle Corp. chose to implement 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.


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 manual which the handling of foreign key constraints and nulls, and references 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 have been declared not null.


  1. Relational databases are not hierarchical, as such FKs do not implement a hierarchy. We may use it for hierarchy, created “parent” and “child”, but that is not what is actually created. An FK is Relational Integrity, it makes sure that the referencing COLUMN specifies what is already there, or nothing at all (perhaps the major use of NULL).

    So, to me, FK aren’t evaluating A=B, but A IN(B). ADD a NOT NULL CONSTRAINT, and then we do have a child which is evaluated as you explain.

    Comment by Brian Tkatch — May 4, 2009 @ 12:12 pm BST May 4,2009 | Reply

  2. Brian,

    I am pretty sure that Jonathan is aware of the fact that the foreign constraint is a set membership predicate rather than a hierarchical construct. The parent-child mental image is just a convenient point of view, nothing more.

    Further, Jonathan is correct in saying that the standard is inconsistent. Consider:

    1. S = {1,2,3} and x= null;

    2. S = {1,2,3, null} and x = 7;

    where S is a set of values against which the foreign constraint is checked and x is the value being checked.

    In both cases the ‘x in S’ predicate evaluates to NULL and yet only the first case passes the constraint. I cannot speculate on why the standard is inconsistent since I did not see any published rationale for such behavior.

    Comment by Val — May 4, 2009 @ 3:28 pm BST May 4,2009 | Reply

  3. Val, good point. Perhaps we can reword the comment though to the SQL of the standard is inconsistent. The meaning, however, is not. An FK checks for existence in a set, regardless of the SQL definition of IN() which treats NULL specially.

    Comment by Brian Tkatch — May 4, 2009 @ 3:52 pm BST May 4,2009 | Reply

    • Brian,

      If you want to say that a foreign key checks for existence in a set then it should be clear that the element (‘Smith’,null) is not a member of the set {(‘Smith’,’John’)}.

      Val is correct, by the way, I am aware of the difference between relational and hierarchical databases. The parent/child example was perhaps not the best choice.

      Comment by Jonathan Lewis — May 5, 2009 @ 5:48 pm BST May 5,2009 | Reply

  4. Oh, I thought you didn’t specify the primary key because I work on an ERP system with thousands of tables… and no primary keys! :-)

    (db blind, integrity in app, blablabla. Does have optional not null in db columns, though.)

    (And the OTN post does say “compost with two columns”, quite an appropriate visualization there. Explanation for our English as a second language friends, that means making fertile soil additive.)

    Comment by joel garry — May 5, 2009 @ 12:35 am BST May 5,2009 | Reply

  5. Joel and Jonathan,

    Do you guys have any idea why Oracle Applications (Oracle ERP) doesn’t have any primary key and foreign key constraints (for version 11.5.9, I don’t know about later version).

    For me it doesn’t make sense that Oracle Applications doesn’t fully use the power offered by Oracle database. I’m missing something ???

    Comment by Statistique — May 5, 2009 @ 6:56 pm BST May 5,2009 | Reply

  6. […] 5th Nov 2009: and here’s another common issue about foreign keys that I also addressed by a link to OTN some time […]

    Pingback by Foreign Keys « Oracle Scratchpad — November 5, 2009 @ 9:31 pm GMT Nov 5,2009 | Reply

  7. […] as the cause of unexpected behaviour. Remember that a constraint accepts a value if the constraint does not evaluate to false. If you put ‘B’ into the constraint definition above, we […]

    Pingback by Null – again « Oracle Scratchpad — September 6, 2012 @ 5:42 pm BST Sep 6,2012 | Reply

  8. […] the new column, then, by definition, the constraint is valid for all the existing records, because NULLs always pass the foreign key constraint check. So in this case it is unnecessary to access each and every record of the table for the validation […]

    Pingback by Optimization of Foreign Key Constraint Creation - DB Oriented — April 13, 2017 @ 2:37 pm BST Apr 13,2017 | Reply

  9. […] standard boolean logic difference between DDL and DML predicates having been already explained (see this and this excellent book) I am not going to repeat that demonstration here. But, for […]

    Pingback by DDL(constraint), DML(predicdate) and SQL CASE | Mohamed Houri’s Oracle Notes — October 20, 2017 @ 8:36 pm BST Oct 20,2017 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by