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))
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));
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);