Re: Recreating unique index for primary key

Ryan Ho <ryanho(at)pacific(dot)net(dot)sg> writes:
> I've dropped an primary key index in order to re-create it. but i realized
> that i can't recreate a primary key index. Will a unique index be an adequate
> replacement? will the database integrity be at risk as a result?

CREATE UNIQUE INDEX is fine as far as the database goes. Offhand it
looks like the only extra thing a primary-key marker does is to define
the default reference column for subsequent foreign-key references
pointing at your table.

If you want, you can reach into pg_index and set the indisprimary field
after creating the index:

update pg_index set indisprimary = true where indexrelid =
(select oid from pg_class where relname = 'yourindexname')

There's been some discussion of adding a PRIMARY option to CREATE INDEX
to allow doing this in a cleaner way, but it's not been done yet.

regards, tom lane