Re: CREATE TABLE LIKE INCLUDING INDEXES support

NikhilS <nikkhils(at)gmail(dot)com> writes:
>> [ remembering previous discussions more clearly... ] Actually there
>> is a concrete problem here: unique constraints are supposed to be
>> represented in the information_schema views, and there is no
>> spec-compliant way to do that for a constraint on something other than
>> a column. We'd have to guess at what the SQL committee would do about
>> that, and the odds of guessing exactly right don't seem encouraging.

> Considering that a unique index is a unique constraint,

No, it isn't. You are confusing definition and implementation. The
spec requires us to do X, Y, and Z in response to the unique-constraint
syntax. It says nothing about what CREATE INDEX does.

> Since this patch is going to consider creating unique/primary indexes
> assuming them to be constraints,

If it does that it will be rejected. There is a difference here and
that difference has to be maintained.

The correct way to think about this is that a pg_constraint entry of
type "unique" or "primary key" has an associated index that is part of
its implementation (and therefore has an "internal" dependency on the
constraint). But they are far from being the same thing.

regression=# create table foo (f1 int unique);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_f1_key" for table "foo"
CREATE TABLE
regression=# drop index foo_f1_key;
ERROR: cannot drop index foo_f1_key because constraint foo_f1_key on table foo requires it
HINT: You can drop constraint foo_f1_key on table foo instead.
regression=# alter table foo drop constraint foo_f1_key;
ALTER TABLE
regression=#

regards, tom lane