Dropping then adding a constraint fails in oracle

Question

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

Answer 1

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.

Answer 2

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;

Answer 3

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;