From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Okay, how about indexes versus unique/primary constraints? |
Date: | 2002-07-11 03:41:04 |
Message-ID: | 13408.1026358864@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
As I currently have Rod's dependency code set up, an index derived from
a UNIQUE or PRIMARY KEY clause can't be dropped directly; you must drop
the constraint instead. For example:
regression=# create table foo (f1 text primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE TABLE
regression=# drop index foo_pkey;
ERROR: Cannot drop index foo_pkey because constraint foo_pkey on table foo requires it
You may DROP the other object instead
regression=# alter table foo drop constraint foo_pkey;
ALTER TABLE
-- now the index is gone, eg
regression=# drop index foo_pkey;
ERROR: index "foo_pkey" does not exist
But on the other hand an index created from CREATE INDEX has no
associated pg_constraint entry, so it can (and must) be dropped with
DROP INDEX.
Is this a good idea, or should we consider the index and the constraint
to be equivalent (ie, you can drop both with either syntax)?
I went out of my way to make the above happen, but now I'm wondering if
it was a good idea or not. Backwards compatibility would suggest
allowing DROP INDEX to get rid of UNIQUE/PRIMARY KEY constraints.
OTOH one might feel that the index is an implementation detail, and
the user should only think about the constraint.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Curt Sampson | 2002-07-11 04:38:42 | Re: Okay, how about indexes versus unique/primary constraints? |
Previous Message | Bruce Momjian | 2002-07-11 03:33:27 | Re: Should this require CASCADE? |