Moving primary key to a new column

[dmg2206]

Is there a way to change which column in a table is the primary key without destroying and recreating the table? I just ran into this issue today and after much searching I resorted to that method (of course I also stored the data in a temp table), but naturally I'm wary of doing that with live data involved. If you know of a safer method I can use for future reference, it'd be much appreciated.

 

[raisin96]

Hi,

I don't think there are other ways (not sure about this) of removing a primary key without recreating the table. Below are currently the functionalities of the ALTER TABLE command based on the HELP of the psql on ALTER TABLE:


Command: ALTER TABLE
Description: Modifies table properties
Syntax:
ALTER TABLE table [ * ]
ADD [ COLUMN ] column type
ALTER TABLE table [ * ]
ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
ALTER TABLE table [ * ]
RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
RENAME TO newtable
ALTER TABLE table
ADD table constraint definitio


Although it does allow you to add a table constraint, it still does not have the functionality of removing/dropping one....maybe on the future releases....

Anyway, another option you could do is to rename the table....since you will not be needing new table/disk space for this as compared to storing it to a temp table..

Good Luck...

Regards,

ABE