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 selectedSQL> 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 :-)
