Unique Constraint with NON-Unique index status
Oracle recommends that unique indexes be created explicitly, and not through enabling a unique constraint on a table.
Note:
A unique constraint does not necessarily create an index.
A unique constraint does not necessarily create a UNIQUE index.
Unique Constraint with Non Unique index
Unique constraint is not depend upon the index, it also used non unique index if it already present on the table.
SQL> create table test6(id number);
Table created.
SQL> create index text6_idx on test6(id);
Index created.
SQL> alter table test6 add constraint test6_cons unique (id);
Table altered.
SQL> Select table_name, index_name, uniqueness from dba_indexes where table_name = 'TEST6';
TABLE_NAME INDEX_NAME UNIQUENES
-------------------- -------------------- ---------
TEST6 TEXT6_IDX NONUNIQUE
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
UNIQUE constraint does not necessarily create an index, it also used multiple column index
SQL> create table test( x int, y int );
Table created.
SQL> create index test_idx on test(x,y);
Index created.
SQL> alter table test add constraint test_unique unique(x);
Table altered.
SQL> Select table_name, index_name, uniqueness from dba_indexes where table_name = 'TEST';
TABLE_NAME INDEX_NAME UNIQUENES
-------------------- -------------------- ---------
TEST TEST_IDX NONUNIQUE
select constraint_name,owner,constraint_type,table_name,index_owner,index_name from dba_constraints where table_name in ('TEST');
CONSTRAINT_NAME OWNER C TABLE_NAME INDEX_ INDEX_NAME
-------------------- ------ - -------------------- ------ -----------
TEST_UNIQUE SYS U TEST SYS TEST_IDX
If no index on table
While adding unique key constraint if no index on table then it create a unique index.
SQL> create table test5(id number);
Table created.
SQL> alter table test5 add constraint kkk unique (id);
Table altered.
SQL> col constraint_name for a20
SQL> col owner for a6
SQL> col table_name for a20
SQL> col index_owner for a6
SQL> col index_name for a20
SQL> select constraint_name,owner,constraint_type,table_name,index_owner,index_name from dba_constraints where table_name = 'TEST5';
CONSTRAINT_NAME OWNER C TABLE_NAME INDEX_ INDEX_NAME -------------------- ------ - -------------------- ------ -------------------- KKK SYS U TEST5 SYS KKK
SQL> Select table_name, index_name, uniqueness from dba_indexes where table_name = 'TEST5';
TABLE_NAME INDEX_NAME UNIQUENES -------------------- -------------------- --------- TEST5 KKK UNIQUE
