Skip site navigation (1) Skip section navigation (2)

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

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #785: 7.3b2 : Possible Inconsistency with DROP INDEX ... CASCADE and DROP CONSTRAINT
Date: 2002-09-27 14:46:27
Message-ID: 20020927144627.88DE9475E2E@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-09-27 15:33:45
Subject: Re: Bug #785: 7.3b2 : Possible Inconsistency with DROP INDEX ... CASCADE and DROP CONSTRAINT
Previous:From: Tom LaneDate: 2002-09-27 14:25:28
Subject: Re: FATAL 1: LWLockAcquire: can't wait without a PROC structure

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group