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:



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

  • Maybe it was created as other type of object and not a unique constraint? Maybe unique index..
    – Marian
    Sep 2, 2011 at 7:55
  • 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. Sep 2, 2011 at 13:08

5 Answers 5


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';


alter table t drop constraint u1;

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


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!

  • 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. Sep 5, 2011 at 6:34

Seems very strange.

You can run:

 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:


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.


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;
  • which db ? including doesn't work in oracle
    – Derrick
    Nov 14, 2018 at 12:01

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;

Do this


It will work.

IMAGE: enter image description here

  • 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;
    – user1822
    Dec 24, 2014 at 11:12
  • 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
    Dec 24, 2014 at 11:24
  • 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
    – Sachin
    Dec 24, 2014 at 11:29
  • 1
    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
    Dec 24, 2014 at 11:53
  • 2
    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.
    – user1822
    Dec 24, 2014 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.