Thread: BUG #1765: Referential Integrity Problem

The following bug has been logged online:

Bug reference:      1765
Logged by:          Herschel Hall
Email address:      herschel.hall@reedyriver.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.

If I change the value of a column in one of the parent's (T_b's) unique
constraint columns, the change will cascade to the child (T_c) ONLY IF the
parent's constraint column that allows nulls, column bk1, IS NOT null.

I have a third table T_a that is the parent of T_b.  T_b has a foreign key
that references T_a's primary key. Changes in T_a cascade to T_b in all
cases.  However they do not cascade to T_c for cases where column bk1 is
null.

Here are table create scripts for the three tables.

CREATE TABLE "T_a"
(
  ak1 varchar(5) NOT NULL,
  CONSTRAINT pk1 PRIMARY KEY (ak1)
)
WITH OIDS;

CREATE TABLE "T_b"
(
  ak1 varchar(5) NOT NULL,
  bk1 varchar(5) NOT NULL,
  bk2 varchar(5),
  CONSTRAINT fk1 FOREIGN KEY (ak1) REFERENCES "T_a" (ak1) ON UPDATE CASCADE
ON DELETE CASCADE,
  CONSTRAINT cs1 UNIQUE (ak1, bk1, bk2)
)
WITH OIDS;

CREATE TABLE "T_c"
(
  ak1 varchar(5) NOT NULL,
  bk1 varchar(5) NOT NULL,
  ck1 varchar(5) NOT NULL,
  bk2 varchar(5),
  CONSTRAINT pkc1 PRIMARY KEY (ak1, bk1, ck1),
  CONSTRAINT fkc1 FOREIGN KEY (ak1, bk1, bk2) REFERENCES "T_b" (ak1, bk1,
bk2) ON UPDATE CASCADE ON DELETE CASCADE
)
WITH OIDS;

If you have any questions, please let me know.

best regards,
Herschel Hall

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.hall@reedyriver.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.