SQL Unique Constraint

ALTER TABLE ADD UNIQUE CONSTRAINT Introduction

 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. 

ALTER TABLE ADD UNIQUE CONSTRAINT Example

First Example:
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.

Second Example:
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