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.)

• Marian: Maybe it was created as other type of object and not a unique constraint? Maybe unique index..
• Adam Butler: Could the initial create have been run with quotes around the table name? This would make the name case sensitive. If so you could drop with quotes and the same case.

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!

• Álvaro González: This was the issue. The dump file generated by the exp command contains a CREATE UNIQUE INDEX "A_DUP_CALLE_UK1" ... statement that's not present in the original script set.

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;

• Derrick: which db ? including doesn't work in oracle

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: enter image description here

• user1822: No it will not work. Your statement is exactly the same statement the first statement in the question: ALTER TABLE A_DUP_CALLE DROP CONSTRAINT A_DUP_CALLE_UK1;
• Sachin: It WORKED actually. I was having same problem since today noon, and searching for the solution I came across this. Sometimes CONSTRAINTS might have been created in a case-sensitive way, in which case, you'll need to put the constraint name in double quotes when you drop it.
• Sachin: And it worked for me. I had not named the constraints explicitly, thus system gave it it's own generated name Relationship142 and other NOT NULL Constraint was given name SYS_C0015910. So SYS_C0015910 was successfully deleted with simple ALTER query, but Relationship142 needed DOUBLE QUOTES
• user1822: The you created the constraints using double quotes, e.g: alter table ... add constraint "Relationship143" ... "Relationship143" is indeed a different name than RELATIONSHIP143. But "RELATIONSHIP143" and RELATIONSHIP143 are identical
• user1822: Oracle (the database) will never create a name like "Relationship143" on its own. It was probably one of your tools that did this. Anyway: as it stands, your answer is simply wrong in the context of the original question.

Answer 6

I had a similar case, where a given constraint was absent, but impossible to create either:

> ALTER TABLE my_tbl drop CONSTRAINT my_cnstr ;
ORA-02443: Cannot drop constraint - nonexistent constraint
> ALTER TABLE my_tbl ADD CONSTRAINT my_cnstr UNIQUE (a, b, c);
ORA-02261: such unique or primary key already exists in the table
> SELECT * FROM user_objects WHERE object_name = 'my_cnstr';
[empty]

Cause was another, redundant constraint on my_tbl covering the same columns, but with a different name. Removing it solved this.