Thanks Tom, I understand now the thinking behind disallowing the drop index
... cascade. If only I'd spent a little bit more time thinking it thru
properly! I do though think the error message could do with a slight change
of wording from:
ERROR: Cannot drop index t1_pkey because constraint c1 on table t1 requires
it
You may drop constraint c1 on table t1 instead
to:
ERROR: Cannot drop index t1_pkey because constraint c1 on table t1 requires
it
To drop index t1_pkey you will need to drop the constraint c1 from
table t1 first
I'd imagine that putting a caveat for foreign key dependencies in the docs
for DROP INDEX would probably cause more confusion than it would help stop!
I have just started learning C so I'm not currently able to offer much,
other than the odd bug report. I hope to change that soon!
As an aside their's an interesting article on WAL's at
http://www.dbazine.com/gulutzan2.html
Best Regards,
Tim Knowles
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 27 September 2002 16:34
To: tim@ametco.co.uk; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Bug #785: 7.3b2 : Possible Inconsistency with DROP
INDEX ... CASCADE and DROP CONSTRAINT
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