How to use UNIQUE Constraint in SQL Server

SQL Server UNIQUE Constraint

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

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Creating a UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the "C_Id" column when "Customers" table is created:

SQL Server / Oracle / MS Access:

CREATE TABLE Customers

(

C_Id int NOT NULL UNIQUE,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

MySQL:

CREATE TABLE Customers

(

C_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

UNIQUE (C_Id)

)

To define a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Customers

(

C_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT uc_CustomerID UNIQUE (C_Id,LastName)

)

Creating a UNIQUE Constraint with ALTER TABLE Statement

To create a UNIQUE constraint on the "C_Id" column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Customers ADD UNIQUE (C_Id)

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Customers ADD CONSTRAINT uc_CustomerID UNIQUE (C_Id,LastName)

To DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Customers DROP CONSTRAINT uc_CustomerID

MySQL:

ALTER TABLE Customers DROP INDEX uc_CustomerID

Thanks, TAMATAM