Convert for Non-Unique to Unique Index value for Unique Constraint in DBA INDEXES

Steps for Non-Unique Index to Unique Index for Unique Constraint

Convert the DBA_INDEXES UNIQUENESS column value to UNIQUE for Unique constraint.

1. Check the index present in table.

Select table_name, index_name, uniqueness from dba_indexes where table_name = 'TEST6';

TABLE_NAME           INDEX_NAME           UNIQUENES
-------------------- -------------------- ---------
TEST6                TEXT6_IDX            NONUNIQUE

2. Check the Constraint detail in table.

SQL> select constraint_name,owner,constraint_type,table_name,index_owner,index_name from dba_constraints where table_name in ('TEST6');

CONSTRAINT_NAME   OWNER  C TABLE_NAME      INDEX_ INDEX_NAME
----------------- ------ - --------------- ------ -----------
TEST6_CONS        SYS    U TEST6          SYS    TEXT6_IDX

3. Drop the existing unique constraint.

SQL> ALTER TABLE TEST6 DROP CONSTRAINT "TEST6_CONS";
Table altered.

4. Drop the index separately because it was already present before the unique constraint created. That’s why it show NON-Unique in DBA_INDEXES uniqueness column.

SQL> Drop index TEXT6_IDX;
Index dropped.

5. Create the Unique Constraint.

SQL> ALTER table TEST6 ADD CONSTRAINT TEST6_CONS UNIQUE(id);

6. Verify index and constraint is created with unique index.

SQL> Select table_name, index_name, uniqueness from dba_indexes where table_name = 'TEST6';

TABLE_NAME           INDEX_NAME           UNIQUENES
-------------------- -------------------- ---------
TEST6                TEST6_CONS           UNIQUE

SQL> select constraint_name, owner, constraint_type, table_name, index_owner, index_name from dba_constraints where table_name in ('TEST6');

CONSTRAINT_NAME   OWNER  C TABLE_NAME      INDEX_ INDEX_NAME
----------------- ------ - --------------- ------ -----------
TEST6_CONS        SYS    U TEST6           SYS    TEST6_CONS

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.