Re: BUG #2412: Foreing key accept nulls

[Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>]

On Fri, 28 Apr 2006, Sidar Lopez Cruz wrote:

>
> The following bug has been logged online:
>
> Bug reference:      2412
> Logged by:          Sidar Lopez Cruz
> Email address:      sidarlopez(at)hotmail(dot)com
> PostgreSQL version: 8.1.3
> Operating system:   Ubuntu 6.04
> Description:        Foreing key accept nulls
> Details:
>
> I think that when I create table that have a foreing key to another one
> these FK may not accept nulls by default.

The SQL spec seems to say otherwise.  A foreign key constraint with match
simple (unspecified) is considered satisfied if any component of the key
is null or there is a match in the referenced table. A foreign key
constraint with match full is considered satisified if all components of
the key is null or there is a match in the referenced table. For a
single column key, this means NULLs are acceptable unless the column has
other constraints (like not null).