Difference Between Unique Indexes and Unique Constraints

There is a very general confusion that whenever we create a unique key constraint or primary key then a corresponding index is created . Primary key and Unique key  creates the unique indexes , but this is not always true .  Lets have a look ...

SQL> create table T1 (id number ) ;
Table created.

SQL> alter table T1 add constraint  T_ID_IDX  unique(id) ;
Table altered.

SQL> select index_name,table_name,uniqueness from dba_indexes where table_name='T1';
INDEX_NAME         TABLE_NAME            UNIQUENES
----------------        -------------------         ---------------
T_ID_IDX                  T1                            UNIQUE

SQL> select constraint_name,constraint_type ,table_name from dba_constraints where table_name='T1' and owner='HR' ;

CONSTRAINT_NAME      C         TABLE_NAME
------------------------      ----       -----------------
T_ID_IDX                      U             T1

Here, we see that when we have created a table unique index get created . Now have another look ..

SQL> create  table  T2 (id number )  ;
Table created.

SQL>  create  unique  index  T2_id_idx  on  T2(id) ;
Index created.

SQL> select index_name,table_name,uniqueness from dba_indexes where table_name='T2'  ;
INDEX_NAME        TABLE_NAME       UNIQUENES
------------------     ------------------      ---------------
T2_ID_IDX                      T2               UNIQUE

SQL>  select constraint_name,constraint_type ,table_name from dba_constraints where table_name='T2' and owner='HR' ;

no rows selected

SQL> alter table T2  add constraint  T2_ID_IDX    unique(id) ;
Table altered.

Now, we expecting  two indexes i.e;  one from the unique index and other from unique constraints . let's look on the below query :

SQL> select constraint_name,constraint_type ,table_name from dba_constraints where table_name='T2' and owner='HR' ;

CONSTRAINT_NAME       C         TABLE_NAME
-------------------------     ----        ------------------
T2_ID_IDX                      U                  T2

SQL> drop index T2_ID_IDX;
drop index T2_ID_IDX
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

Above query show only one indexes  . Hence from the above demo, we can only say that  " a unique constraint does not necessarily create an index or a unique constraint does not necessarily create a UNIQUE index "  .

If  we want a unique index in place, it is suggested we should explicitly create it by  using CREATE UNIQUE INDEX .  A primary key or unique constraint is not guaranteed to create a new index, nor is the index they create guaranteed to be a unique index.  Therefore, if  we desire a unique index to be created for query performance issues, we should explicitly create one. 

A question may arises that why do we need a unique constraint when we already have a unique index?
The reason are

1. ) The difference between a unique index and a unique  constraint starts with the fact that the constraint is a rule while the index is a database object that is used to provide improved performance in the retrieval of rows from a table. It is a physical object that takes space and is created with the DDL command  .

2.) we can use either a unique OR non-unique index to support a unique constraint. Constraints are metadata, more metadata is good. We can define a foreign key to a unique constraint, not so a unique index. 

3.) A constraint has different meaning to an index. It gives the optimiser more information and allows us to have foreign keys on the column  whereas a unique index doesn't. But most importantly because  it is the right way to do it. 

Enjoy     :-)