Moving primary key to a new column
Moving primary key to a new column
(OP)
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.
RE: Moving primary key to a new column
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