Re: BUG #2412: Foreing key accept nulls

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).