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

User · Answer

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

User · Answer

Sometimes when we do these steps    alter table my table drop constraint my pk    alter table my table add constraint my pk primary key  city id  buildtime  time     The last statement fails with        ORA-00955  name is already used by an existing object    Oracle usually creates an unique index with the same name my pk  In such a case you can drop the unique index or rename it based on whether the constraint is still relevant   You can combine the dropping of primary key constraint and unique index into a single sql statement   alter table my table drop constraint my pk drop index     check this  ORA-00955  quot name is already used by an existing object quot

User · Answer

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

[sql] Change Primary Key

Examples related to sql

Examples related to oracle

Examples related to primary-key