unique constraint automatically

 

 

ALTER SESSION SET CURRENT_SCHEMA = GCD

/

 

 

ALTER TABLE GCD.PTRSLIMIT

    DROP CONSTRAINT UK_PTRSLIMIT

/

 

begin

        execute immediate 'drop index UK_PTRSLIMIT';

        exception

                when others then

                        null;

end;

/

 

ALTER TABLE GCD.PTRSLIMIT

    ADD ( CONSTRAINT UK_PTRSLIMIT

                UNIQUE (PTRSID, LIMITREFERENCEID, SEQ)

                NOT DEFERRABLE INITIALLY IMMEDIATE )

/

 

===

Since UK_PTRSLIMIT is a unique constraint, DB create a unique constraint and a index both;

So we need to drop constraint and index both;

 

When we create UK_PTRSLIMIT again, it’s a unique constraint too, so DB will create both constraint and index too.

So we don’t need to create index again.

 

We can use below SQL to check our change

=======================================================================================

select * from all_constraints

where CONSTRAINT_NAME=upper('UK_PTRSLIMIT')

  and owner=upper('GCD')

/

 

select * from user_indexes where INDEX_NAME='UK_PTRSLIMIT'

/

 

select * from user_ind_columns where index_name=upper('UK_PTRSLIMIT')

/

 

select *  

from user_constraints

where CONSTRAINT_NAME = upper('UK_PTRSLIMIT')

/