Change Primary Key

sql oracle primary-key
Mohit BAnsal picture Mohit BAnsal · Feb 22, 2010 · Viewed 160.3k times · Source

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

Peter Lang picture Peter Lang · Feb 22, 2010

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.

Related questions

Best way to reset an Oracle sequence to the next value in an existing column?

For some reason, people in the past have inserted data without using sequence.NEXTVAL. So when I go to use sequence.NEXTVAL in order to populate a table, I get a PK violation, since that number is already in use …

Read More
Superkey, candidate key & primary key

Can any kind soul clarify my doubts with a simple example below and identify the superkey, candidate key and primary key? I know there are a lot of posts and websites out there explaining the differences between them. But it …

Read More
Referencing a composite primary key

I have two tables, with each table having a composite primary key. One attribute is in both composite primary keys. How am i supposed to reference the common attribute?? Do i just reference it as a FK in both tables …

Read More