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.
