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

On Thu, Dec 9, 2010 at 2:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> writes:
> > But I still hold a bias towards renaming the index to match constraint
> name
> > (with a NOTICE), rather than require that the constraint name match the
> > index name, because the constraint name is optional and when it is not
> > provided system has to generate a name and we have to rename the index
> > anyway to maintain consistency.
>
> No. If the constraint name is not specified, we should certainly use
> the existing index name, not randomly rename it.
>
>
Attached is the updated patch with doc changes and test cases. An overview
of the patch is in order:

The new command syntax is

ALTER TABLE table_name
ADD [CONSTRAINT constraint_name]
PRIMARY KEY USING INDEX index_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE
];

ALTER TABLE table_name
ADD [CONSTRAINT constraint_name]
UNIQUE USING INDEX index_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE
];

The index should be a unique index, and it should not be an expressional or
partial index. The included test cases exercise a few other cases.

If the constraint name is provided, then index is renamed to that with a
NOTICE, else the index name is used as the constraint name.

I have consciously disallowed the ability to specify storage_parameters
using the WITH clause, if somebody thinks it is wise to allow that and is
needed, I can do that.

Git branch: https://github.com/gurjeet/postgres/tree/constraint_with_index

Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh(dot)gurjeet(at){ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device