> I have a need to provide a way to change a table's primary key > columns, in the possible presence of foreign keys, and with concurrent > use (as much as possible). >
> The best way I have come up with is roughly: > a. create the to-be-new-pk index concurrently > b. "demote" the old pk to be a unique constraint > c. alter table add constraint new pk using existing index > > Step b. involves (in one transaction): > - -------------------------------------- > * update pg_class row for the table > relhaspkey false > * update pg_constraint row for the original pk > contype = 'u' > conname = 'some_new_name' > * update pg_index row for the original pk index > indisprimary = false > * alter index original pk rename to some_new_name > - -------------------------------------- > I have tested this (minimally) and as far as I can tell it works. > > Questions: > 1) any major holes in this approach? > 2) any better ideas? > 3) should we consider an ALTER TABLE ALTER CONSTRAINT command > (or some such) to allow demotion of a PRIMARY KEY to a UNIQUE > constraint? > > Thanks for any thoughts/comments. >
Hi Joe,
I already did something like it once, but to rebuild a bloated PK index with minimal locks. I still waiting for 'REINDEX CONCURRENTLY' :-)
At that time I didn't have no trouble with this approach.
