Cannot drop nonexistent constraint and cannot create it either

Question

While testing some migration scripts with a copy of production data (scripts run fine with development data) I found a curious situation. A CONSTRAINT has changed so I'm issuing DROP + ADD commands:

ALTER TABLE A_DUP_CALLE
DROP CONSTRAINT A_DUP_CALLE_UK1;

ALTER TABLE A_DUP_CALLE
ADD CONSTRAINT A_DUP_CALLE_UK1 UNIQUE (
    CONTROL_ID,
    CALLE_AYTO_DUPL
)
ENABLE;

The DROP command worked fine but the ADD one failed. Now, I'm into a vicious circle. I cannot drop the constraint because it doesn't exist (initial drop worked as expected):

ORA-02443: Cannot drop constraint - nonexistent constraint

And I cannot create it because the name already exists:

ORA-00955: name is already used by an existing object

I type A_DUP_CALLE_UK1 into SQL Developer's Search box and... there it is! Owner, table name, tablescape... everything matches: it isn't a different object with the same name, it is my original constraint. The table appears in the constraint details but the constraint does not appear in the table's details.

My questions:

• What's the explanation for this?
• How can I ensure it won't happen when I make the real upgrade in live server?

(Server is 10g XE, I don't have enough reputation to create the tag.)

Answer 1

At a guess I'd say Marian is right and this is caused by a unique index and constraint having the same name, eg:

create table t( k1 integer, k2 integer, 
                constraint u1 unique(k1,k2) using index(create unique index u1 on t(k1,k2)),
                constraint u2 unique(k2,k1) using index u1);

select count(*) from user_indexes where index_name='U1';

COUNT(*)               
---------------------- 
1  

alter table t drop constraint u1;

select count(*) from user_indexes where index_name='U1';

COUNT(*)               
---------------------- 
1  

Normally when you add a unique constraint, a unique index with the same name is created - but the index and constraint are not the same thing. Have a look at all_indexes to see if there is an index called A_DUP_CALLE_UK1 and try and figure out if it is used by something else before you drop it!

Answer 2

Seems very strange.

You can run:

 SELECT *
 FROM user_objects
 WHERE object_name = 'A_DUP_CALLE_UK1'

to check if what kind of object that is Oracle complains about. Then you can run the approriate DROP statement for that.

The only other thing I can think of is to drop the table entirely using DROP TABLE A_DUP_CALLE CASCADE CONSTRAINTS to get rid of everything that belongs to that table and then re-create it completely.

If the table contains valueable data you can make a backup of it before:

CREATE TABLE old_data
AS
SELECT *
FROM A_DUP_CALLE;

Once you have recreated the table, you can do

INSERT INTO A_DUP_CALLE (col1, col2, col3) 
SELECT col1, col2, col3
FROM old_data

to restore the data.

Answer 3

I've had the same problem just a few minutes ago... and I've found an explanation.

By creating a Primary Key, Oracle creates two objects : a constraint, and an index, that controls the "UNIQUE" part.

By dropping the constraint, the index remains there, using the same name of the index, so if you execute just

alter table t drop constraint u1;

You'll be dropping only the constraint. To drop the index, you'll need to execute

drop index u1;

This should do the work. Alternatively, you could do both of these commands at the same time with the command

alter table t drop constraint u1 including indexes;

Answer 4

Primary key constraint come with index. You drop constraint but not index. Check:

select * from ALL_OBJECTS where OBJECT_NAME = 'PK_TBL_CONSTR';

and you see OBJECT_TYPE is INDEX.

So do both:

alter table TBL drop constraint PK_TBL_CONSTR;
drop index PK_TBL_CONSTR;

Answer 5

Do this

ALTER TABLE A_DUP_CALLE
DROP CONSTRAINT "A_DUP_CALLE_UK1";

It will work.

IMAGE: