SQL UNIQUE Constraint

A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique-that is, no two rows of a table have duplicate values in a specified column or set of columns.

Unique Key constraint is same as primary key i.e. it does not accept duplicate values, except the following differences:

• There can be only one Primary key per table. Whereas, you can have as many Unique Keys per table as you want.

• Primary key does not accept NULL values whereas; unique key columns can be left blank.

It depends on situations; first situation is suppose you have already defined a Primary key constraint on one column and now you have another column which also should not contain any duplicate values, since a table can have only one primary key, you can define Unique Key constraint on these columns.

Second situation is when a column should not contain any duplicate value but it should also be left blank.

Syntax: At column Level:

(Column_name data type [Constraint <name>] UNIQUE,

Syntax: At table Level:

[Constraint <name>] UNIQUE (<column_name>);

Query: The following SQL enforces the “P_Id” column to take unique values.
SQL> CREATE TABLE Persons(p_Id number (6) UNIQUE,LastName varchar2 (15),FirstName varchar2 (15),Address varchar2 (25),City varchar2 (15))

Table created.

Query: Table level

SQL> CREATE TABLE Persons(P_Id number (6),LastName varchar2 (15),FirstName varchar2 (15),Address varchar2 (25),City varchar2 (15)Email varchar2(20), constraint UN_persons UNIQUE (email));

Table created.

Query: To define a UNIQUE KEY constraint on an existing table give the following command.
SQL> Alter table v add constraint UN_v unique(roll); OR
SQL> Alter table v add unique (roll);