Oracle unique constraint remove and modify

Question

I have one table and 2 constraint-- out of which one constraint is trivial and i wanted to remove it from existing this table--which has data.

So below is table --

create table t1 (aa varchar2(10),bb varchar2(10),cc varchar2(10),dd varchar2(10),ee varchar2(10));


insert into T1 values ('a','b','c','x','y');
insert into T1 values ('d','e','f','u','w');
insert into T1 values ('g','h','i','q','r');
insert into t1 values ('j','k','l','v','z');


alter table T1 add constraint T1_U unique (AA,BB);


alter table T1 add constraint T1_U1 unique (cc,dd);

Now, we have 2 constraints and out of those, i wanted to remove T1_U1 and modify T1_U.

2 constraints can be seen via--

SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME IN ('T1_U','T1_U1');

Now below are the steps which i am following

1. Disable T1_U constraint--

   ALTER TABLE T1 DISABLE CONSTRAINT T1_U;

2. Renaming index --

   ALTER INDEX T1_U1 RENAME TO T1_U;

3. Dropping T1_U.

   ALTER TABLE T1 DROP CONSTRAINT T1_U;

Now, here if i check USER_OBJECTS still i can see T1_U. -- Why it is so?

So i tried to use DROP INDEX T1_U; -- which is not correct.

4. Then i tried to modify T1_U-- which got failed as expected.

Question: can you please let me know is there any way we can achieve above?

Thanks.

Answer 1

This is easily explained if you also look at user_constraints:

create table t1 (aa varchar2(10),bb varchar2(10),cc varchar2(10),dd varchar2(10),ee varchar2(10));

insert into T1 values ('a','b','c','x','y');
insert into T1 values ('d','e','f','u','w');
insert into T1 values ('g','h','i','q','r');
insert into t1 values ('j','k','l','v','z');

alter table T1 add constraint T1_U unique (AA,BB);
alter table T1 add constraint T1_U1 unique (cc,dd);

SELECT object_name, object_type FROM USER_OBJECTS WHERE OBJECT_NAME IN ('T1_U','T1_U1');

OBJECT_NAME  OBJECT_TYPE        
------------ -------------------
T1_U         INDEX              
T1_U1        INDEX 

select constraint_name, constraint_type, table_name, status, index_name from user_constraints where constraint_name IN ('T1_U','T1_U1');

CONSTRAINT_NAME  CONSTRAINT_TYPE TABLE_NAME  STATUS   INDEX_NAME 
---------------- --------------- ----------- -------- -----------
T1_U             U               T1          ENABLED  T1_U
T1_U1            U               T1          ENABLED  T1_U1

Constraints are present along with their corresponding indexes.

ALTER TABLE T1 DISABLE CONSTRAINT T1_U;

SELECT object_name, object_type FROM USER_OBJECTS WHERE OBJECT_NAME IN ('T1_U','T1_U1');

OBJECT_NAME  OBJECT_TYPE        
------------ -------------------
T1_U1        INDEX

select constraint_name, constraint_type, table_name, status, index_name from user_constraints where constraint_name IN ('T1_U','T1_U1');

CONSTRAINT_NAME  CONSTRAINT_TYPE TABLE_NAME  STATUS   INDEX_NAME 
---------------- --------------- ----------- -------- -----------
T1_U1            U               T1          ENABLED  T1_U1      
T1_U             U               T1          DISABLED

Now constraint T1_U is disabled, and the index T1_U that was enforcing the index is now no longer required, and - because Oracle knows that the constraint was created by the constraint and not separately (I'm not sure exactly how, but it does) - it knows that the index is no longer required so it can be dropped. (You can confirm this by creating the index first before creating the constraint, and when the constraint is disabled, the index is still there.)

ALTER INDEX T1_U1 RENAME TO T1_U;

SELECT object_name, object_type FROM USER_OBJECTS WHERE OBJECT_NAME IN ('T1_U','T1_U1');

OBJECT_NAME  OBJECT_TYPE        
------------ -------------------
T1_U         INDEX

select constraint_name, constraint_type, table_name, status, index_name from user_constraints where constraint_name IN ('T1_U','T1_U1');

CONSTRAINT_NAME  CONSTRAINT_TYPE TABLE_NAME  STATUS   INDEX_NAME 
---------------- --------------- ----------- -------- -----------
T1_U1            U               T1          ENABLED  T1_U       
T1_U             U               T1          DISABLED

Because there is no index T1_U anymore, we can rename the T1_U1 index to T1_U. Note, however, that this doesn't alter which constraint it is associated with - it still belongs to the T1_U1 constraint.

ALTER TABLE T1 DROP CONSTRAINT T1_U;

SELECT object_name, object_type FROM USER_OBJECTS WHERE OBJECT_NAME IN ('T1_U','T1_U1');

OBJECT_NAME  OBJECT_TYPE        
------------ -------------------
T1_U         INDEX     

select constraint_name, constraint_type, table_name, status, index_name from user_constraints where constraint_name IN ('T1_U','T1_U1');

CONSTRAINT_NAME  CONSTRAINT_TYPE TABLE_NAME  STATUS   INDEX_NAME 
---------------- --------------- ----------- -------- -----------
T1_U1            U               T1          ENABLED  T1_U

Therefore, when you drop the T1_U constraint, which no longer has an associated index, nothing other than the T1_U constraint is dropped. The renamed T1_U index belongs to the T1_U1 constraint, therefore, you wouldn't expect it to be dropped.

Moreover, if you tried to do now:

drop index t1_u;

you'd get:

ORA-02429: cannot drop index used for enforcement of unique/primary key

Hopefully this clears things up for you regarding your first question.

For your second question - "can you please let me know is there any way we can achieve above?" it depends exactly on what you're trying to do. Hopefuly my explanation above has allowed you to work out what is going on in your example and why, and enabled you to answer your own question.

If it hasn't, please update your question with more information about what you're trying to achieve.