how to create SQL unique constraint

We will discuss about the SQL unique constraint and difference between Primary Key and Unique key and demonstrate how to create/drop unique constraint

The SQL unique constraint ensures that all values in a columns are unique.

By default Primary Key constraint also granted the uniqueness of columns values and the only difference is that primary key does not accept NULL value but unique key constraint accept a single NULL value in column.

Difference between Primary Key Vs Unique Key:

·         Primary Key does not accept NULL value but Unique Key do

·         Only one Primary key per table but we can create multiple unique key per table.

Here is a table OrderStatus, which contains Primary Key on StatusID

Create Table OrderStatus

(

       StatusID int IDENTITY(1,1) NOT NULL,

       StatusCode varchar(3) NOT NULL,

       StatusDescription varchar(50) NOT NULL,

       CONSTRAINT [OrderStatus_PK] PRIMARY KEY CLUSTERED ( StatusID ASC)

)

Syntax: Create SQL Unique Constraint on Create Table:

The below SQL script will create a unique constraint  ‘OrderStatusCode_UC  ’ on  a Column StatusCode in table and it ensures that Column should not duplicate Code.

Create Table OrderStatus

(

       StatusID int IDENTITY(1,1) NOT NULL,

       StatusCode varchar(3) NOT NULL,

       StatusDescription varchar(50) NOT NULL,

       CONSTRAINT [OrderStatus_PK] PRIMARY KEY CLUSTERED ( StatusID ASC),

       CONSTRAINT OrderStatusCode_UC UNIQUE (StatusCode)

)

Syntax: Create SQL Unique Constraint on Alter Table:

The below SQL script will create a unique constraint ‘OrderStatusCode_UC’ on already existing table

ALTER TABLE OrderStatus

ADD CONSTRAINT OrderStatusCode_UC UNIQUE (StatusCode);

The below SQL script will create a unique constraint ‘OrderStatusDescCode _UC’ on multiple columns

ALTER TABLE OrderStatus

ADD CONSTRAINT OrderStatusDescCode_UC UNIQUE (StatusCode, StatusDescription);

Drop SQL Unique Key Constraint:

You can simply use below SQL script to Drop unique constraint

ALTER TABLE OrderStatus

DROP CONSTRAINT OrderStatusDescCode_UC;