UNIQUE INDEX

Unique index is also a part of indexes which guarantee that no two rows of a table have duplicate values in the columns that define the index.

Unique index is automatically created when primary key or unique constraint is created.

It will not allow duplicate values.

When ever we create unique index, internally unique constraint will create.

Syntax:-

Create unique index  on  (column name);

Example:-

SQL> create unique index na_uniq_deptno on dept (deptno);

-- Index created.

Creating unique index on multiple columns

SQL> create unique index na_uniq_deptno on dept (dname, loc);

--index created

When ever we create unique index, system will internally create unique constraint on the table column

Example:-

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

Now get the records from dept table

SQL>select *from dept;

By seeing above records we can see that there is record already available with LOC as ‘CHICAGO’.

Now we will try to insert same value (CHICAGO) into dept table in LOC column.

SQL>insert into dept values (50,'TESTING','CHICAGO');

Output-

Insert into dept values (50,'TESTING','CHICAGO')

*

ERROR at line 1:

ORA-00001: unique constraint (APPS.NA_IDX) violated

System will raise an error because we had created unique index in that particular column (LOC)

For dropping index

Syntax:-

Drop index ;

Example:-

SQL>Drop index idx7;

-- Index dropped