Обсуждение: Okay, how about indexes versus unique/primary constraints?

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

On Wed, 10 Jul 2002, Tom Lane wrote:

> 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.
> ...
> I went out of my way to make the above happen, but now I'm wondering if
> it was a good idea or not.

I think it's a great idea. It helps make it clear just why the index was
created, so you don't get someone less familiar with the schema saying,
"we don't have any queries that use this index, so we might as well get
rid of it...."

I think this change is hardly likely to cause problems, since adding or
deleting indexes seems unlikely to be automated. It's really a system
administration activity, not something the application would do on its own.

> OTOH one might feel that the index is an implementation detail, and
> the user should only think about the constraint.

Exactly my feeling.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org   Don't you know, in this new Dark Age, we're
alllight.  --XTC