Bug #785: 7.3b2 : Possible Inconsistency with DROP INDEX ... CASCADE and DROP CONSTRAINT

Tim Knowles (tim(at)ametco(dot)co(dot)uk) reports a bug with a severity of 4
The lower the number the more severe it is.

Short Description
7.3b2 : Possible Inconsistency with DROP INDEX ... CASCADE and DROP CONSTRAINT

Long Description
I don't know if this is intended behaviour but as the 7.3 devel docs advise that DROP INDEX ... CASCADE should drop dependent objects I though I'd bring it to someones attention. Basically DROP INDEX ... CASCADE will not cascade the drop to any dependent foreign keys. You can though use ALTER TABLE ... DROP CONSTRAINT ... CASCADE which will cascade the drop.

Sample Code
CREATE TABLE t1 (
col_a int,
PRIMARY KEY (col_a)
);
CREATE TABLE t2 (
col_b int,
CONSTRAINT c1 FOREIGN KEY (col_b) REFERENCES t1(col_a)
);

--DROP INDEX WILL COMPLAIN ABOUT DEPENDENT OBJECTS

DROP INDEX t1_pkey;

--DROP INDEX t1_pkey CASCADE WILL NOT CASCADE THE DROP TO DEPENDENT OBJECTS

DROP INDEX t1_pkey CASCADE;

--ALTER TABLE t1 DROP CONSTRAINT t1_pkey WILL ALSO COMPLAIN ABOUT DEPENDENT OBJECTS

ALTER TABLE t1 DROP CONSTRAINT t1_pkey;

--ALTER TABLE .. DROP .. CASCADE WILL CASCADE THE DROP TO THE FOREIGN KEY CONSTRAINT

ALTER TABLE t1 DROP CONSTRAINT t1_pkey CASCADE;

No file was uploaded with this report