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.

Answer 2

You will need to drop and re-create the primary key like this:

alter table my_table drop constraint my_pk; alter table my_table add constraint my_pk primary key (city_id, buildtime, time); 

However, if there are other tables with foreign keys that reference this primary key, then you will need to drop those first, do the above, and then re-create the foreign keys with the new column list.

An alternative syntax to drop the existing primary key (e.g. if you don't know the constraint name):

alter table my_table drop primary key;