In this SQL Tutorial I will show you how to create a Unique Constraints. For the purpose of this tutorial I will use SQL Server 2012.
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server 2008 and R2
- SQL Server 2005
- We suspect that this will work in SQL Server 2005 as well
Who is it for?
- People who would like to learn basics of using a unique constraint.
- Those who are preparing for Microsoft certifications and need to cover this topic.
We use UNIQUE CONSTRAINT to make sure that we don't allow any duplicated entries in a column.
I prepared two examples:
- In my first example I will show you how to set up UNIQUE Constraint on EmailAddress field. The purpose of this example is to not allow user to entry the same EmailAddress more than once. In other words EmailAddress must be unique.
- The second example I create unq_PersonConstraint to minimize the risk of entering the same person twice. We will only allow new records if FirstName, LastName and BirthDate is unique.
- NOTE: Sometimes this can occur so you should make sure you don't restrict users from entering valid records.
ALTER TABLE ADD UNIQUE CONSTRAINT Syntax:
ALTER TABLE TableName
ADD CONSTRAINT ConstraintName UNIQUE (FieldName)
In the Object Explorer I open required database in this case it is AdventureWorksDW2012 then I expand Tables folder and find table which I will use to show ALTER TABLE ADD UNIQUE CONSTRAINT example. In this case it is dbo.DimCustomer table.
I open Object Explorer and go to dbo.DimCustomer table, Columns folder to check the field I use. In this case it is EmailAddress.
In new query I type the code to create unq_EmailAddress constraint.
I run the query and In the messages window I can see that my query was run successfully. My unq_EmailAddress was created.
In this example I show you how to create unq_Person and in this case I use three fields FirstName, LastName, BirthDate.
I create new query and type my code to create unq_Person constraint. You can see below.
In Messages window I get information that my query was run successfully. My unq_Person constraint was created.
Those two constraints were created in a dbo.DimCustomer table in a Keys folder (NOT Constraints folder)
I hope that helps