Not able to add a new column to the existing primary key

Question

I have a primary key PK1 for the table TABLE1. Need to add one more column to the existing primary key. I was tying the following alter script

ALTER TABLE TABLE1
ADD CONSTRAINT "PRIMARYKEYS" PRIMARY KEY
("PK1",
"PK2");

Error report:
SQL Error: ORA-02260: table can have only one primary key
02260. 00000 -  "table can have only one primary key"
*Cause:    Self-evident.
*Action:   Remove the extra primary key.

How can I add one more column to the primary key, without affecting the data(data has been verified , there are no duplication.)

possible duplicate of [Change Primary Key Oracle](http://stackoverflow.com/questions/2310561/change-primary-key-oracle) — Noel, Mar 25 '14 at 06:38

score 2 · Accepted Answer · answered Mar 25 '14 at 06:42

If you have concern about new data that violates PK can be added during the time interval when the old PK dropped, but the new one is not created, you can create a unique index first :

CREATE UNIQUE INDEX IDXU_TABLE1_PK ON TABLE1(PK1,PK2);
ALTER TABLE TABLE1 DROP CONSTRAINT [old_pk_constraint_name] ;
ALTER TABLE TABLE1 ADD CONSTRAINT "PRIMARYKEYS" PRIMARY KEY
   (PK1,PK2) USING INDEX IDXU_TABLE1_PK;

Another option is to keep index associated with the old PK constraint until new PK is created :

ALTER TABLE TABLE1 DROP CONSTRAINT [old_pk_constraint_name] KEEP INDEX;
ALTER TABLE TABLE1 ADD CONSTRAINT "PRIMARYKEYS" PRIMARY KEY
   (PK1,PK2) ;
DROP INDEX [name of unique index associated with the old PK constraint];

score 0 · Answer 2 · answered Mar 25 '14 at 06:31

0

You need to drop old primary key first.

answered Mar 25 '14 at 06:31

Suor

2,845
1
22
28

Not able to add a new column to the existing primary key

2 Answers2