The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

To create a table with a column that has the UNIQUE constraint attached, one must follow this syntax:

Syntax

CREATE TABLE customer (Cust_id NUMBER(2) NOT NULL UNIQUE,

 LastName VARCHAR2(14),

 FirstName VARCHAR2(14) NOT NULL,

 Address VARCHAR2(20),

 Telno NUMBER(20) UNIQUE

 );

Next example shows how to name UNIQUE constraints and defining a constraint on multiple columns:

CREATE TABLE customer (Cust_id NUMBER(2) NOT NULL,

   LastName VARCHAR2(14),

   FirstName VARCHAR2(14) NOT NULL,

   Address VARCHAR2(20),

   Telno NUMBER(20),

   CONSTRAINT unique_const UNIQUE (Cust_id,telno)

   );

And this example will show us how to add a UNIQUE constraint to already existing table:

ALTER TABLE Customer ADD UNIQUE (FirstName);

In case if we want to get rid of an old constraint, following syntax will do:

ALTER TABLE Customer DROP CONSTRAINT unique_const;