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

Tim Knowles (tim@ametco.co.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
dependentobjects I though I'd bring it to someones attention.  Basically DROP INDEX ... CASCADE will not cascade the
dropto any dependent foreign keys.  You can though use ALTER TABLE ... DROP CONSTRAINT ... CASCADE which will cascade
thedrop. 

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

pgsql-bugs@postgresql.org writes:
> CREATE TABLE t1 (
>   col_a int,
>   PRIMARY KEY (col_a)
> );

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

> --ALTER TABLE .. DROP .. CASCADE WILL CASCADE THE DROP TO THE FOREIGN KEY CONSTRAINT
> ALTER TABLE t1 DROP CONSTRAINT t1_pkey CASCADE;

This is deliberate: you created the index indirectly via a constraint,
so you should drop the constraint rather than dropping the index itself.
Essentially, the index is only an implementation detail that you should
not be messing with directly.

Or at least that was the design idea.  If you think this is wrongheaded,
feel free to start a discussion about it on pghackers.

It might be that the behavior is okay but the error message should be
phrased differently in this case.  Any thoughts?

            regards, tom lane