How are unique Indexes and unique Constraints Related?

Unique Index:-

When ever we create a unique index it will create an unique index which does not allow to insert duplicate values as well as unique constraint will also create internally by system but we can not view the constraint.

Example:-

SQL>create unique index na_loc_idx on dept (loc);

-- index created

Index name and on which table we had created index those are We can see it in below command

SQL> SELECT INDEX_NAME, INCLUDE_COLUMN, INDEX_TYPE, UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME LIKE 'DEPT';

<<<<<<<<>>>>>>>>>>

Here we can see the unique index name but not unique constraint. Actually unique constraint  will created by system internally

Unique Constraint:-

When we create a unique constraint on a table of the column, a unique constraint will be created as well as a unique index also create in that column which we can view .

First we will create a unique constraint on a table of column………

Syntax :-

Alter table  add constraint  unique() ;

Example :-

SQL>alter table dept add constraint na_uniq_cons unique(loc);

-- table altered

Now we will check whether constraint is created or not by going to user_constraints.

SQL>select *from user_constraints where table_name =’DEPT’;

<<<<<<<<>>>>>>>>>

Here we can see unique constraint is created on ‘LOC’ column of the table ‘DEPT’.

Now we will see whether unique index is created or not by going to user_indexes

SQL>Select *from user_indexes where table_name=’DEPT’

<<<<<<<>>>>>>>>>

Here we can see that index is also created.

So, we can conclude that unique index will create when ever we create unique constraint.