Add SQL Server Unique Constraint on Multiple Columns

This SQL tutorial shows how to add SQL Server unique constraint on multiple columns in a database table. SQL unique constraint on multiple columns ensures that in SQL Server table there will not be a second row sharing the same data on defined columns with an other table row.

For example if you have a system where you manage and track reservations of meeting rooms, you will want to prevent reservations of a resource for the same time slot for different requestors. As a database desing solution, this case a unique constraint that will keep your database table data consistent. I assume that in your application's database model you have a reservations time table. In that case an SQL Unique Constraint multiple columns are defined in the column list will throw exception while adding a second record with same that on defined columns.


Alter Table tablename Add Constraint constraintname UNIQUE (column1, ..., columnn)

Code

Let's make a few examples on adding unique constraint in SQL Server database table on multiple columns and test if unique constraint is violated when we insert same column data in two different rows.

Unique Constraint Example


Create Table Reservation (

 Id int identity(1,1),

 Resource varchar(10),

 TimeSlot varchar(20),

 Requestor varchar(50)

)

Code

Assume that SQL developer or SQL Server database administrator did not create unique constraint on above database table on columns Resource and TimeSlot, it will be possible to reserve the same meeting room to different groups for the same time period. Here is the two database Insert statements to test the case where SQL uniqu constraint is not added.


INSERT INTO Reservation SELECT 'Meeting101', '10:00-11:00', 'SQL Development Team'

INSERT INTO Reservation SELECT 'Meeting101', '10:00-11:00', 'SQL Server Administration Team'

Code

This is a case which violates our database design requirements.
Let's continue to the same example case. Let me add SQL Server unique constraint on multiple columns (Resource and TimeSlot columns) using below Transact-SQL command.


Alter Table Reservation Add Constraint sqlUniqueConstraint UNIQUE (Resource, TimeSlot)

Code

Although the add unique constraint syntax is correct and the multiple column list contains correct sql table columns, sql developers will get the following exception thrown by SQL Engine.

could not create unique constraint in SQL Server
SQL unique constaint fails because of dublicate key was found in target table

SQL Execution Error.
Executed SQL statement:
Alter Table Reservation Add Constraint sqlUniqueConstraint UNIQUE (Resource, TimeSlot)
Error Source: .Net SqlClient Data Provider Error Message: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Reservation' and the index name 'sqlUniqueConstraint'. The duplicate key value is (Meeting101, 10:00-11:00).
Could not create constraint. See previous errors.
The statement has been terminated.

In fact the above error message is enough self explaining. Just because of the existing table data contains non-unique key values, the unique constraint can not be created for that key columns. So before continue to add unique constraint to SQL Server table, you should somehow remove or change duplicate key column values. It is nice that the exception in this example (I run SQL command within Visual Studio 2012), the key column values that violate unique constraint is also returned to the user who run the command.

Let's see the rows that prevent unique constraint creation and violate our unique constraint


Select * From Reservation Where Resource = 'Meeting101' And TimeSlot ='10:00-11:00'

Code

duplicate key column values in sample sql database table

I'll update meeting room for SQL Server Administrators team to Meeting102. This will prevent unique contraint violation error during we add unique constraint on table for multiple columns.


Update Reservation Set Resource = 'Meeting102'

 Where Requestor = 'SQL Server Administration Team'

Code

After updating table data to provide a unique initial situation before we add unique constraint on multiple columns of table, we are ready to execute the Alter Table table_name Add Constraint constraint_name Unique (key_column_list) script once more.


Alter Table Reservation Add Constraint sqlUniqueConstraint UNIQUE (Resource, TimeSlot)

Code

This time SQL Server database engine will successfully add new unique constraint covering given multiple columns list on target table.

After uniqu constraint is created, developers can not insert any record to the table containing duplicate key with existing table data. Let's make a sample case which violate unique constraint on SQL Server table and see what happens. Execute following Insert commands, please note that we have again duplicate keys here for resource and time-slot columns.


INSERT INTO Reservation SELECT 'Meeting102', '11:00-12:00', 'SQL Development Team'

INSERT INTO Reservation SELECT 'Meeting102', '11:00-12:00', '.NET Programmers'

Code

Although the first row is successfully inserted to the database table, the second command will fail because it has duplicate values with the first one.

SQL Server error: Violation of unique key constraint

Error Message: Violation of UNIQUE KEY constraint 'sqlUniqueConstraint'. Cannot insert duplicate key in object 'dbo.Reservation'.
The statement has been terminated.

Above sample shows that unique constraint helps SQL Server administrators and SQL developers to keep data validity on their database applications.