Re: foreign constraint with unique key

From: David Portas <REMOVE_BEFORE_REPLYING_dportas_at_acm.org>
Date: Tue, 3 Feb 2009 22:40:50 -0000
Message-ID: <esudnUdH88NhWhXUnZ2dnUVZ8u2dnZ2d_at_giganews.com>



"Norbert Winkler" <norbert.winkler1_at_gmx.de> wrote in message news:1v3o377vzqhj5.1ll05haaw4xyw.dlg_at_40tude.net...
> Hi,
>
> I've tried to create a foreign key on two columns, where the second col
> should be nullable to avoid a trigger for testing integrity.
>
>

A foreign key that includes a nullable column does not compare nulls between the two tables. Instead a multi-column constraint will never be violated on a row that contains a null - even if the value(s) in the other column(s) are not present in the referenced table. Nullable foreign keys are pretty useless in most cases.

I suggest you do without a nullable foreign key. Have one table with the constraint on one column and another with the constraint on two.

-- 
David Portas
Received on Tue Feb 03 2009 - 16:40:50 CST

Original text of this message