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.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Moving primary key to a new column
- Thread starter dmg2206
- Start date
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
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
- Status
Similar threads
- Locked
- Question
- Locked
- Question