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.