Re: BUG #1765: Referential Integrity Problem

On Wed, 13 Jul 2005, Herschel Hall wrote:

> The following bug has been logged online:
>
> Bug reference: 1765
> Logged by: Herschel Hall
> Email address: herschel(dot)hall(at)reedyriver(dot)com
> PostgreSQL version: 7..4
> Operating system: Linux
> Description: Referential Integrity Problem
> Details:
>
> I have a parent table T_b that contains a unique constraint and no primary
> key. One of the columns (bk1) referenced in the unique constraint allows
> nulls.
>
> I have a child table T_c that has a foreign key that references the parent
> table's unique constraint columns.

bk1 was defined as not null, did you mean bk2?

However, the behavior you are seeing is correct. A row in "T_c" that has
a NULL in the key fields explicitly does not reference the row in "T_b"
that has a NULL in the same position given the definitions. In SQL92 at
least the "matching row" definition for the unspecified match type seems
to say "let matching rows be all rows in the referencing table whose
referencing column values equal the corresponding referenced column values
for the referential constraint". Since NULLs are not equal to anything,
the row in "T_b" with a NULL does not have any matching rows to act upon
in "T_c". And, for the unspecified match type, a row (blah, blah, NULL)
is considered to pass the constraint, even if there are no rows with
(blah, blah) in the referenced table.

MATCH FULL would require that all columns in the referencing tables were
not null or all NULL. MATCH PARTIAL allows mixing where (blah, blah,
NULL) effectively references any (blah, blah, something), but we don't
support that yet.