Unique constraint prevents duplicates and ensures that there is only one distinct value of their own in a column. Perhaps, the most widely used constraint in a database to maintain data integrity is Primary key. Though, practically a table can have only one primary key. Therefore, to enforce further uniqueness in other columns we use Unique key constraint.
We can have multiple unique constraints in a table compared to one primary key. When we create a unique constraint on a column or group of columns, SQL server validates that a column will not have a single value repeated twice. In case you forcefully try to insert a duplicate value, SQL Server will raise a Unique constraint violation error.
Unique Constraints – Points to Remember
1. A table can have multiple unique constraints rather than having one primary key.
2. Unique constraints can be created on multiple columns to maintain uniqueness likewise composite primary key.
3. Unique constraint will allow having a single NULL value in a table.
4. SQL Server adds a Unique Non-Clustered Index on a column having Unique constraint favouring faster result retrieval using Index.
Comparing Unique Constraint to Primary Key Constraint
1. Primary key constraint restricts usages of NULL values whereas Unique Constraint allows a
single NULL value.
2. By default SQL Server allows multiple Unique constraints per table whereas a single primary
key constraint can be created per table.
3. SQL Server adds a Unique Clustered index with primary key and Unique Non-clustered index with
Unique constraint by default.
Creating Unique Constraints On Single Column
Let’s walk through with few examples on how to use Unique constraints to enforce uniqueness in a table having a primary key.
The following query creates a Country(CountryCode, Name, Population) table. Including a primary key on CountryCode column and unique constraint on Name column. Enforcing a rule to have Unique country names in a table along with unique Country codes. Notice that NOT NULL constraint added to Name column avoiding any NULL values.
Always a good habit to name your constraint with friendly names like UK_Country_Name for Unique constraint instead of allowing SQL Server to create a one with random and non-readable names. The following query first creates a Country table and Inserts 3 rows successfully.
USE Tempdb; GO --Check If Table Exists Then Drop IF OBJECT_ID('Country','U') IS NOT NULL DROP TABLE Country; GO --Create Country Table with Unique Constraint on Name Column CREATE TABLE Country ( CountryCode CHAR(3) NOT NULL ,Name VARCHAR(25) NOT NULL ,Population INT NOT NULL ,CONSTRAINT PK_Country_CountryCode PRIMARY KEY(CountryCode) ,CONSTRAINT UK_Country_Name UNIQUE(Name) ); GO --Successfully Inserted 3 Rows INSERT INTO Country VALUES ('NPL', 'Nepal', 29791280) ,('CHN', 'China', 1417679878) ,('IND', 'India', 1361775454); GO --Successful 3 Rows SELECT * FROM Country;
Result Set
Unique Constraint Violation Error
While trying to insert the 4th row, we have intentionally added unique countrycode along with the duplicate name “India”, therefore resulting in a violation of unique key constraint causing an error.
Msg 2627, Level 14, State 1, Line 25
Violation of UNIQUE KEY constraint ‘UK_Country_Name’. Cannot insert duplicate key in object ‘dbo.Country’. The duplicate key value is (India). The statement has been terminated.
USE tempdb; GO --Violates Unique Key Constraint Due to Duplicate Country Name INSERT INTO Country VALUES ('IDN','India', 2202013);
Result Set
Creating Unique Constraints On Multiple Column
In the above example, we had created a unique constraint on a single column to enforce uniqueness. However, it’s possible to add a unique constraint on multiple columns.
In the following query, we have created Player (Name, Trophy) table. Alongside, adding a unique constraint on the combination of Name and Trophy columns to have a composite unique constraint. SQL Server will validate that values are always unique in the combination of two columns instead of one.
Referring to the values we can notice that “Federer”, “Aus Open” and “Federer”, “US Open” is allowed. However, “Federer”, “US Open” is considered as duplicate violating the composite unique constraint rule.
USE Tempdb; GO --Check If Table Exists Then Drop IF OBJECT_ID('Player','U') IS NOT NULL DROP TABLE Player; GO --Create Player Table with Composite Unique Constraint --on Name, Trophy Column CREATE TABLE Player ( Name VARCHAR(50) NOT NULL ,Trophy VARCHAR(25) NOT NULL ,CONSTRAINT UK_Player_NameTrophy UNIQUE(Name,Trophy) ); GO --Successfully Inserted 3 Rows INSERT INTO Player VALUES ('Federer' ,'Aus Open') ,('Federer' ,'US Open') --,('Federer', 'US Open') --*Not Allowed - Violation* ,('Nadal' ,'French Open') ,('Djokovic' ,'Wimbledon') ,('Djokovic' ,'US Open'); GO --Successful 3 Rows SELECT * FROM Player;
Result Set
Creating Unique Constraints On Existing Table
In the following example, we are creating a unique constraint on an existing table which already has a primary key constraint and 3 rows. Let’s reuse the Country table from the first example. Create Country (CountryCode, Name, Population) having a primary key on CountryCode column.
Adding a unique constraint later using ALTER TABLE statement. Pay careful attention, if the existing table has duplicate values in a column, before adding a unique constraint, as duplicate values won’t allow a unique constraint to be added on that column resulting in a violation of a rule.
USE Tempdb; GO --Check If Table Exists Then Drop IF OBJECT_ID('Country','U') IS NOT NULL DROP TABLE Country; GO --Create Country Table with Primary Key On CountryCode CREATE TABLE Country ( CountryCode CHAR(3) NOT NULL ,Name VARCHAR(25) NOT NULL ,Population INT NOT NULL ,CONSTRAINT PK_Country_CountryCode PRIMARY KEY(CountryCode) ); GO --Successfully Inserted 3 Rows INSERT INTO Country VALUES ('NPL', 'Nepal', 29791280) ,('CHN', 'China', 1417679878) ,('IND', 'India', 1361775454); GO --Add Unique Constraint On Existing Table ALTER TABLE Country ADD CONSTRAINT UK_Country_Name UNIQUE(Name); --Successful 3 Rows SELECT * FROM Country;
Dropping Unique Constraints
The following query shows using DROP statement to remove a Unique constraint on a table. Before we remove a unique constraint, best practice to check if a constraint exists then drop it else ignore the DROP statement.
IF OBJECT_ID('UK_Country_Name', 'UQ') IS NOT NULL ALTER TABLE Country DROP CONSTRAINT UK_Country_Name; GO
Comparing Unique Constraint to Unique Index
Perhaps, it’s vital to understand the primary difference between Unique constraint and Unique Indexes in SQL Server. Essentially both options enforce uniqueness on a column. However, we can use both of them interchangeably as per database design and needs.
1. Unique constraint and Unique index both enforce uniqueness on a column.
2. If our primary requirement is to add a Unique index on a column with a goal to enable faster results. Adding unique index also gives us the opportunity to INCLUDE non-indexed columns or add a filtered unique index which is not possible while adding the unique constraint.
3. Under the hood, Unique constraint adds a non-clustered unique index after creating a unique constraint on a column, but we are not allowed to INCLUDE any non-indexed columns or modify it to create a filter index.
4. We can’t disable a unique constraint. Although it’s allowed to disable a unique index when needed.
Summary
In this tech-recipes post we have learned the use of Unique constraint in SQL Server to enforce uniquiness in a table when a primary key is already been added. Adding a Unique constraint in existing table, adding composite unique constraint and dropping it. If you like this post you may browse through Tech-Recipes Database Archive posts to learn more useful stuff.
