Unique Constraint with NON-Unique index status

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

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.