How to create Unique Constraint on Multiple Columns in SQL Server - SQL Server / TSQL Tutorial Part 96

Scenario:

You are working as SQL Server Developer, you are asked to provide create scripts for dbo.Customer table with Unique Constraint on First Name and Last Name columns.

Solution:

As we know that the Unique Constraint in SQL Server is created on a column or columns to restrict the column/s to accept only unique values.

The below script can be used to create Unique Constraint on multiple columns in our case FirstName and LastName.

USE [YourDatabaseName]
GO
CREATE TABLE [dbo].[Customer](
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [SSN] VARCHAR(11),
    Constraint UQ_Dbo_Customer_FName_LName Unique(FirstName,LastName)
) 

--Let's insert some sample data to test Unique Constraint 
Insert into dbo.Customer(FirstName,LastName,SSN)
Values(Null,Null,'000-00-0000'),
('Aamir',Null,'000-00-0000'),
(Null,'Aamir','000-00-0000')
,('Aamir','Shahzad','000-00-0001')
,('Raza','M','000-00-0002')


If you have noticed the insert statement, In First Record I have put both Null values for FirstName and Last Name. In 2nd record I used the value 'Aamir' for first name and Null for Last Name. In 3rd record vice versa of 2nd record. From here what we understand that the unique constraint is working on both column values. As long as the combined value from both columns is unique, it is going to let us insert even one of them is null.

If I would like to insert another record in which I have Null for last name, it will let me without any problem, as it is unique from existing values.

Insert into dbo.Customer(FirstName,LastName,SSN)
Values('Raza',Null,'000-00-0000')



Let's check the records in table by using select query.
How to add Unique Constraint on Multiple Columns in SQL Server Table
If we try to insert a duplicate records, we will get below error.

Insert into dbo.Customer(FirstName,LastName,SSN)
Values('Raza',Null,'000-00-0000')


Msg 2627, Level 14, State 1, Line 30
Violation of UNIQUE KEY constraint 'UQ_Dbo_Customer_FName_LName'. Cannot insert duplicate key in object 'dbo.Customer'. The duplicate key value is (Raza, ).
The statement has been terminated.



Video Demo : Create Unique Constraint on Multiple Columns in SQL Server


No comments:

Post a Comment