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

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;