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

On Fri, Dec 03, 2010 at 05:16:04PM -0500, Robert Treat wrote:
> On Fri, Dec 3, 2010 at 4:41 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
> > However, I don't see why we need (column_list). Surely the index has a
> > column list already?
> >
> > ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY USING index_name
> >
> > ... seems like the syntax most consistent with the existing commands.
> > Anything else would be confusingly inconsistent with the way you add a
> > brand-new PK.
> >
> >
> Uh, the syntax I posted was based on this currently valid syntax:
>
> ALTER TABLE table_name ADD PRIMARY KEY (column_list);
>
> The constraint bit is optional, which is why I left it out, but I presume it
> would be optional with the new syntax as well... Also, I'm not wedded to the
> idea of keeping the column list, but if you are arguing to make it super
> consistent, then I think you need to include it.

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.

Ross
--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
Connexions http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE