:::: MENU ::::

How to Add unique key constraint

  • Apr 21 / 2009
  • 0
Constraints and Keys, dbDigger, T-SQL Scripts

How to Add unique key constraint

Primary key is used to avoid duplication of records in a table. There may be business requirements where single column or group of columns other than primary key are required to be unique. Along with implementing this logic in application code it would be better to implement this constraint at database layer.
To go on with a little example create a test table as

 
USE AdventureWorks  
 GO  
 CREATE TABLE testTable  
 (RecordID SMALLINT IDENTITY(1,1) PRIMARY KEY,  
 courseName VARCHAR(30),  
 startDate DATETIME,  
 endDate DATETIME)  
 GO  

TestTable is now there with total four columns. RecordID is primary key with identity property. Now it is required that there should be unique course name and start date when combined. To implement this logic let us alter our table to add unique key constraint for two columns [recordID] and [startDate]

 
USE AdventureWorks  
 GO  
 ALTER TABLE testTable  
 ADD CONSTRAINT UK_testTable_testinguniquekeys  
 UNIQUE NONCLUSTERED (courseName,startDate)  
 GO  

Now let us check that our constraint is implementing the required logic of unique keys

 
USE AdventureWorks  
 GO  
 INSERT INTO TestTable (courseName,startdate,enddate)  
 VALUES ('Course1','2009-04-21','2009-06-15')  
 GO  
 INSERT INTO TestTable (courseName,startdate,enddate)  
 VALUES ('Course1','2009-04-21','2009-06-20')  
 GO  

First row is inserted successfully but there was following error for second row because courseName and startDate were same as first row.

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint ‘UK_testTable_testinguniquekeys’. Cannot insert duplicate key in object ‘dbo.TestTable’.
The statement has been terminated.

Similarly unique key constraint may be implemented for a single column or for group of columns. Click here for another example of creating unique key constraint through SSMS.

Consult us to explore the Databases. Contact us