SQL-UNIQUE Constraint

UNIQUE Constraint

By default, if you don't specify the UNIQUE constraint, a table column can not hold duplicate values.

Unique key constraint enforces that the column can contains only unique values on which unique constraints is defined. This column can contain multiple null values but all not null values must be unique. Unique Constraint can be defined either at the column level or at the table level.

The following example create a table with constraints for the columns: FirstName when the Employee table is created:

CREATE TABLE Employee (
Eid int NOT NULL,
FirstName varchar(100) UNIQUE,
LastName varchar(100) UNIQUE,
Emailid varchar(100) NOT NULL,
Age int,
Departmentid int
);

Output

Now insert Some data in Employee table.

Now Try to insert column duplicate value in FirstName Column. It show the following message.

Assign name to a UNIQUE Constraint

To assign a particular name to a UNIQUE constraint, you use the CONSTRAINT keyword as follows:

CREATE TABLE Employee (
Eid int NOT NULL,
FirstName varchar(100) UNIQUE,
LastName varchar(100) UNIQUE,
Emailid varchar(100) ,
Age int,
Departmentid int,
CONSTRAINT unique_email UNIQUE(emailid)
);

The following are the benefits of assigning a UNIQUE constraint a specific name:

1. It easier to classify the error message.
2. You can reference the constraint name when you want to modify it.

UNIQUE Constraints for a Group of Columns

To define a UNIQUE constraint for a group of columns, you write it as a table constraint with column names separated by commas as follows:

CREATE TABLE Employee (
Eid int NOT NULL,
FirstName varchar(100),
LastName varchar(100),
Emailid varchar(100),
Age int,
Departmentid int,
UNIQUE (FirstName,LastName) 
);

Adding UNIQUE Constraint to Existing Columns

To add the UNIQUE constraint to an existing column, you should follow these simple steps:

First - update the table so there is no duplicate value in the column:

UPDATE Employee 
SET emailid =  -- Define any value
WHERE Eid=;

Second - To create a UNIQUE constraint on the Emailid column when the Employee table is already created, use the following SQL Query:

ALTER TABLE Employee 
ADD CONSTRAINT unique_email UNIQUE(Emailid);

Removing UNIQUE Constraint

To remove the UNIQUE constraint from a column, you can use the ALTER TABLE ALTER COLUMN statement as follows:

ALTER TABLE Employee 
DROP CONSTRAINT Emailid;

Here you will learn what is a primary key and how to create it in a new or existing table in the SQL Server database.