Cannot drop nonexistent constraint and cannot create it either

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!


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.


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;