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
objectinstead
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