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;