sql - Change Primary Key

Question

I have a table in Oracle which has following schema:

City_ID  Name  State  Country  BuildTime  Time

When I declared the table my primary key was both City_ID and the BuildTime, but now I want to change the primary key to three columns:

City_ID  BuildTime  Time

How can I change the primary key?

Answer 1

Assuming that your table name is city and your existing Primary Key is pk_city, you should be able to do the following:

ALTER TABLE city
DROP CONSTRAINT pk_city;

ALTER TABLE city
ADD CONSTRAINT pk_city PRIMARY KEY (city_id, buildtime, time);

Make sure that there are no records where time is NULL, otherwise you won't be able to re-create the constraint.