SQL Unique Key Constraint

A unique key constraint is a set of one or more than one columns of a table that uniquely identify a record in a database table.

It is similar like a primary key but it can accept only one null value and it cannot have duplicate values.

The unique key and primary key both provide a guarantee for uniqueness for a column or a set of columns.

However, we can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Syntax

CREATE TABLE table_name (
col1 datatype1,
col2 datatype2,
constraint constraintname UNIQUE(column name list)
)

Create a Unique Key constraint when create a new table

Lets create a new table named as OrderDetails and add unique key constraint on column OrderCode.

To create a UNIQUE key constraint on OrderCode column, use the following SQL create table statement.

CREATE TABLE dbo.OrderDetails(
OrderId INT PRIMARY KEY, 
OrderCode VARCHAR(10),
OrderDate DATETIME,
CONSTRAINT CONS_OrderDetails_OrderCode UNIQUE(OrderCode)
)

 

To verifying whether the Unique key constraint is created or not on table column, use the sp_help stored procedure.

EXEC sp_help OrderDetails

As you can see the output of stored procedure sp_help, unique key is created on column OrderCode.

Lets, check the implementation of unique key, first we will insert a single record into OrderDetails table.

INSERT INTO OrderDetails
(OrderId,OrderCode,OrderDate)
VALUES
(1, 'ODNO45', GETDATE())

Now we have one record in table OrderDetails as shown below.

SELECT * FROM dbo.OrderDetails

Now we try to insert duplicate value in OrderCode means try to insert an existing value in OrderCode column, as you can see we have already a value ‘ODNO45’ in OrderCode column.

Lets Insert the same value again.

INSERT INTO OrderDetails
(OrderId,OrderCode,OrderDate)
VALUES
(2, 'ODNO45', GETDATE())

You can see, for inserting a duplicate value in unique key column gives an error.

Unique key constraint allows only single null value

Unique key constraint can accept only one null value and it cannot have duplicate values as well as null values.

Lets insert a null value in OrderCode column.

INSERT INTO OrderDetails
(OrderId,OrderCode,OrderDate)
VALUES
(2, null, GETDATE())

As you can see, command completed successfully.  As it was first null value into this column so unique key allowed.

Lets check table to verify same, and you can see there is one null value in OrderCode column.

SELECT * FROM dbo.OrderDetails

Now we have one null value in OrderCode column, Lets try to insert duplicate null value for column OrderCode.

INSERT INTO OrderDetails
(OrderId,OrderCode,OrderDate)
VALUES
(3, null, GETDATE())

As you can see, it gives an error saying ‘can not insert duplicate key in object Orderdetails. The duplicate key value is (<null>).’

Drop unique Key constraint from an existing table

To drop the unique key from column OrderCode you can use Alter table statement for drop constraint. You need to specify constraint name that you want to drop.

If you do not know the constraint name you can use sp_help stored procedure to get the constraint name.

EXEC sp_help OrderDetails

As we can see, the name of Unique key constraint that is created on OrderCode is CONS_OrderDetails_OrderCode.

Also you can remember this constraint name is that you have provided to constraint at the time creating a new table and added a unique key constraint.

Now you know the constraint name, Lets use Alter table statement to drop the constraint from column.

ALTER TABLE OrderDetails
DROP CONSTRAINT CONS_OrderDetails_OrderCode

To verifying whether unique key is removed or not on table column, you can use sp_help stored procedure or can try to insert any duplicate values or null values in ordercode column and it should allow duplicate values.

As you can see, there is no unique key constraint on table now.

Lets insert duplicate null value for OrderCode.

You can see, null values is inserted in ordercode column that means Unique key constraint is removed from column.

SELECT * FROM dbo.OrderDetails

Creating a Unique Key on an existing table

As you have just removed the Unique key on OrderCode column, lets create it again.

Before creating a unique key on an existing table column, you have to make sure that data in that column follows all the rules of unique key that is all records must be unique, only single null values can be accepted. Unique key can not be created on column that already have duplicate values.

As table have already duplicate null values in OrderCode column that can be seen in above screenshot, and if you try to create a unique key constraint on OrderCode column it will give an error as shown below.

ALTER TABLE dbo.OrderDetails 
ADD CONSTRAINT CONS_OrderDetails_OrderCode UNIQUE(OrderCode)

Lets remove the duplicate records from table  then try to re-create unique key.

So, here we have duplicate null values in OrderCode column, we will delete one value that is for OrderId =3.

DELETE FROM dbo.OrderDetails WHERE OrderId =3;
SELECT * FROM dbo.OrderDetails

You can no table does not have duplicate records in column OrderCode, Lets try re- create unique key on column OrderCode now.

ALTER TABLE dbo.OrderDetails 
ADD CONSTRAINT CONS_OrderDetails_OrderCode UNIQUE(OrderCode)

As you can see , commands is executed successfully, you can verify the same using sp_help stored procedure.

Creating unique key constraint on multiple columns

You can also create Unique key on multiple columns, using below syntax.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (col1, col2,... )

Also Read..

Foreign Key constraint

Primary Key VS composite Key

Primary key Constraint