"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 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.
Thoughts?
regards, tom lane