Reputation: 8722
I'm trying to move a primary key constraint to a different column in oracle. I tried this:
ALTER TABLE MY_TABLE
DROP CONSTRAINT c_name;
ALTER TABLE MY_TABLE
ADD CONSTRAINT c_name PRIMARY KEY
(
"COLUMN_NAME"
) ENABLE;
This fails on the add constraint with an error saying the the constraint already exists even though i just dropped it. Any ideas why this is happening
Upvotes: 6
Views: 10771
Reputation: 4022
The safest way is to first add a unique index. Try this:
create unique index new_pk on <tabname> (column_2);
Then drop the old PK:
alter table <tabname> drop primary key drop index;
(Optional) Rename the index:
alter index new_pk rename to c_name;
And then add the PK again indicating the index to be used:
alter table <tabname> add constraint c_name
primary key (column_2) using index c_name;
Upvotes: 2
Reputation: 7161
If the original constraint was a primary key constraint, Oracle creates an index to enforce the constraint. This index has the same name as the constraint (C_NAME in your example). You need to drop the index separately from the constraint. So you will need to do a :
ALTER TABLE <table1> DROP CONSTRAINT C_NAME;
DROP INDEX C_NAME;
ALTER TABLE <table1> ADD CONSTRAINT C_NAME PRIMARY KEY
( COLUMN_2 ) ENABLE;
Upvotes: 11
Reputation: 882446
I don't know if this is a similar problem but, in DB2, you have to actually commit following the alter table
statement. Otherwise it's still hanging around.
Upvotes: 0