The following bug has been logged online: Bug reference: 1765 Logged by: Herschel Hall Email address: [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
