Re: Patch to add a primary key using an existing index

On Dec 4, 2010, at 1:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
>> If you consider that an index basically is, in some sense, a pre-canned
>> column list, then:
>
>> ALTER TABLE table_name ADD PRIMARY KEY (column_list);
>> ALTER TABLE table_name ADD PRIMARY KEY USING index_name;
>
>> are parallel constructions. And it avoids the error case of the user
>> providing a column list that doesn't match the index.
>
> +1 for that approach.  One other thought is that ordinarily, the
> add-constraint syntax ensures that the constraint is named the same as
> its underlying index; in fact we go so far as to keep them in sync if
> you rename the index later.  But after
>
> ALTER TABLE table_name ADD CONSTRAINT con_name PRIMARY KEY USING index_name;
>
> they'd be named differently, unless we (a) throw an error or (b)
> forcibly rename the index.  Neither of those ideas seems to satisfy the
> principle of least surprise, but leaving it alone seems like it will
> also lead to confusion later.

I think that might be the best way though.

> I wonder whether, in the same spirit as not letting the user write a
> column name list that might not match, we should pick a syntax that
> doesn't allow specifying a constraint name different from the index
> name.  In the case where you say CONSTRAINT it'd be a bit plausible
> to write something like
>
> ALTER TABLE table_name ADD CONSTRAINT con_name PRIMARY KEY USING EXISTING INDEX;
>
> (implying that the index to use is named con_name) but I don't know
> what to do if you want to leave off the "CONSTRAINT name" clause.

Because this seems plain weird.