UNIQUE KEY Constraint

Since one table can have at the most one primary key constraint , to avoid the duplicates from other columns we can use UNIQUE key constraint

• UNIQUE key column do not accept duplicate value.It can accept NULL value.
• One table can have a number of UNIQUE key constraint
• A composite UNIQUE key constraint is always declare in table level.
• Oracle create an Index Implicitly on the UNIQUE key column.

Restrictions :

• UNIQUE key con't be implemented on columns having  LOB, LONG, VARRAY, NESTED TABLE, OBJECT, TIMESTAMP WITH TIME ZONE.
• A composite UNIQUE key constraint cannot have more then 32 columns
• Same column or combination of columns we cant be design as both PRIMARY key and UNIQUE key constraint

Syntax :

Column Level UNIQUE Constraint declaration.

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH) UNIQUE,
COLUMN_NAME2 <DATA_TYPE>(WIDTH) CONSTRAINT constraint_name UNIQUE,
COLUMN_NAMEn <DATA_TYPE>(WIDTH));

Table level UNIQUE Constraint declaration.

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH),
COLUMN_NAME2 <DATA_TYPE>(WIDTH),
COLUMN_NAMEn <DATA_TYPE>(WIDTH),
CONSTRAINT constraint_name UNIQUE (COLUMN_NAME2));

Composite UNIQUE key constraint declaration.

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH),
COLUMN_NAME2 <DATA_TYPE>(WIDTH),
COLUMN_NAMEn <DATA_TYPE>(WIDTH),
CONSTRAINT constraint_name UNIQUE (COLUMN_NAME1,COLUMN_NAME2));


See The Example:

UNIQUE CONSTRAINT EXAMPLE