There may be required to make sure that to fulfill a certain business logic, each record should be unique in terms of some columns. To implement this there is no need to create any primary key having combination of columns. Simply a unique key constraint may be created.
To consider an example of creating unique key constraint let us create a sample table
USE AdventureWorks GO CREATE TABLE TestUKey(serNumb SMALLINT IDENTITY(1,1), name VARCHAR(50), city VARCHAR(50), phone VARCHAR(13), cellNumb VARCHAR(13)) GO
Suppose now it is required to make sure that each record have unique combined values in name and cellnumb. To implement this constraint we may use following T-SQL statement
[/sql] USE AdventureWorks
ALTER TABLE TestUKey
ADD CONSTRAINT IX_TestUKey_NameCell
Our required logic has been implemented and to confirm it following is a part of result from <
To create unique key constraint through SQL Server Management Studio (SSMS), following are required steps.
Drop existing unique key constraint so that we may create unique key constraint through SSMS
USE AdventureWorks GO ALTER TABLE TestUKey DROP CONSTRAINT IX_TestUKey_NameCell GO
Now we are ready to create unique key constraint through SSMS
- Right click on table on which constraint will be implemented and go to Design
- Right click on left bar of design to access indexes and keys or access from tool bar
- A frame will appear. Choose both columns and select unique key in type.
- Close the frame and exit from designer.
Unique key has been created and this may be verified through SSMS
I have noticed that along with each unique key created a non clustered index is automatically created comprising the columns of unique key.
Click here for another example of unique key constraint implementation.