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|
Insert into dbo.Customer(FirstName,LastName,SSN) Values('Raza',Null,'000-00-0000')