Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Maybe it was created as other type of object and not a unique constraint? Maybe unique index.. –  Marian Sep 2 '11 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. –  Adam Butler Sep 2 '11 at 13:08

5 Answers 5

up vote 12 down vote accepted

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!

share|improve this answer
    
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. –  Álvaro G. Vicario Sep 5 '11 at 6:34

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.

share|improve this answer

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 these commands at the same time with the command

alter table t drop constraint u1 including indexes;

share|improve this answer

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;
share|improve this answer

Do this

ALTER TABLE A_DUP_CALLE
DROP CONSTRAINT "A_DUP_CALLE_UK1";

It will work.

IMAGE: enter image description here

share|improve this answer
    
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; –  a_horse_with_no_name Dec 24 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 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 at 11:29
    
A quoted identifier where all characters are upper case is identical to the same name without quotes (because Oracle stores all un-quoted identifiers in upper case). –  a_horse_with_no_name Dec 24 at 11:31
1  
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. –  a_horse_with_no_name Dec 24 at 12:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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