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.

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.

8 Comments »

  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 BST 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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,506 other followers