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
