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
