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.