SQL Server Unique Constraints

In SQL Server, constraints are certain rules that can enforce data integrity on database tables. In this article, we will explore the types of constraints available in SQL Server and learn more about unique key constraints in detail. We will also look into the similarities and significant differences between primary key constraints and unique key constraints and similarly between unique key constraints and unique indexes.

Types of Constraints

Let’s take a quick look into all types of constraints available to understand the significance of unique key constraints.

• Primary Key Constraint
• Foreign Key Constraint
• Unique Key Constraint
• Check Constraint

Primary Key Constraint

Every normalized table should have a column or set of columns to uniquely identify the data row present inside a table and these unique single column or combination of columns will be referred to as the primary key for that specific table. Since primary keys are used to enforce the uniqueness of data inside a table, there are certain prerequisites or restrictions for creating primary keys on a table.

• Primary key columns won’t allow NULL values and hence single column or multiple column that uniquely forms the primary key should be defined as NOT NULL.
• A table can have only one primary key defined out either on a single column or multiple columns together.
• Creation of primary key on a table creates a clustered index by default unless otherwise manually specified as non-clustered index. Clustered index physically sorts the data in the table with the index pointing to the actual block with the leaf nodes pointing to the actual data. Non-clustered index logically sorts the records based upon the clustered index (if available) on the table and stores the actual value and a pointer to the actual row that holds the data with the leaf nodes pointing to the included columns.
• Primary key constraint can be created on a combination of 16 columns maximum however the length of the key columns should not exceed 900 bytes. Primary keys are recommended to create on lesser columns and mostly on identity columns for better performance.

Foreign Key Constraint

Foreign key constraints are used to enforce data integrity in a child table by verifying the data available in the primary key of another table. In other words, foreign key constraints create a relationship from one table to another and reference the data present in the primary key of the master table. As foreign key constraint references the primary key of the master table, it should be created on the same set of a single column or multiple columns used by the primary key.

Unique Constraint

Unique key constraints are used to enforce the uniqueness of data among the selected single column or multiple columns similar to the Primary Key constraint with the below differences.

• Unique key constraint columns can be created on nullable columns with the exception that those columns can hold only one NULL value per column involved. Hence the columns involved in unique key constraint can be defined as NOT NULL.
• A table can have more than one unique key constraint defined on set of columns.
• Creation of unique key constraint creates a unique index on those columns by default. Unique index will be a non-clustered index unless otherwise manually created as a clustered index provided that there wasn’t any clustered index defined on that table.

CHECK Constraint

The CHECK constraint is used to enforce data integrity on the selected columns by limiting restrictions on the involved columns. Restrictions that can be posed by CHECK constraint can be either a logical expression or range of values or across multiple columns.

Now that we have understood the type of constraints, we can learn about unique key constraints in more detail in this article.

Creating the Unique Key Constraint

Unique key constraints can be created either during the time of table creation using the CREATE TABLE statement or after the table is created by using the ALTER TABLE statement. The syntax for creating a unique key constraint would be:

Via Create Table statement

CREATE TABLE Employee (
	EmpId int IDENTITY NOT NULL,
	EMPNo int NOT NULL,
	CONSTRAINT PK_EmpNo PRIMARY KEY(EmpID),
	CONSTRAINT UQ_EmpNo UNIQUE(EmpNo)

Via ALTER TABLE Statement

CREATE TABLE Employee (
	EmpId int IDENTITY NOT NULL,
	EMPNo int NOT NULL
	);

ALTER TABLE Employee ADD CONSTRAINT PK_EmpNo PRIMARY KEY(EmpID);
ALTER TABLE Employee ADD CONSTRAINT UQ_EmpNo UNIQUE(EmpNO);

Once the unique key constraint is created either via the CREATE TABLE or ALTER TABLE statement, we can see the below items in SSMS.

Even though we have just created the primary key and unique key constraints only, we can see a clustered index with the name of primary key constraint and a unique non-clustered index with the name of unique key constraint were created on the table.

Let’s try adding a new column named SSN and a unique key constraint to the Employee table and then check the table structure in SSMS.

ALTER TABLE Employee ADD SSN VARCHAR (10) NULL;

ALTER TABLE Employee ADD CONSTRAINT UQ_SSN UNIQUE(SSN);

Primary Key vs Unique Key Constraint

Even though primary key and unique key constraints ensure uniqueness on the columns involved, they are significant differences between primary keys and unique key constraints which we can compare with the Employee table we have created.

1. A table can have only one primary key constraint whereas it can have multiple unique key constraint. In our case, primary key constraint is defined on EmpId and have 2 unique key constraint on EmpNo and SSN columns.
2. Primary key constraint can be created only on NOT NULL columns like EmpId whereas unique key constraint can be created on EMPNo which doesn’t accept NULL values and on SSN column which can accept NULL values. Even though SSN column (as per above table design) can accept NULL values, it can accept only one NULL value since unique key constraint is defined on that column.
3. Creation of primary key constraint creates a clustered index by default if no clustered indexes are available on that table whereas creation of unique key constraint creates a non-clustered index by default on the columns involves unless specified as a clustered index manually.
4. Primary key clustered index won’t display as unique even though it enforces uniqueness on the columns involved.

Unique Key Constraint vs Unique Indexes

Even though the creation of a unique key constraint creates a unique Index internally, there are few differences between a unique key constraint and a unique index and to demonstrate that let’s create a unique index manually on the SSN column on the Employee table using the script below:

CREATE UNIQUE INDEX IX_SSN on Employee (SSN);

Once the unique index is created, we can see the below table structures in SSMS.

1. We can notice the difference in Icon for the unique index created using a CREATE INDEX statement compared to the key icon for the unique key constraint created using a CREATE constraint using either the CREATE TABLE or ALTER TABLE option.
2. Unique Index IX_SSN can be dropped out directly using DROP INDEX command as shown below

DROP INDEX IX_SSN ON Employee;

However, we can’t drop the unique index UQ_SSN listed under indexes using the DROP INDEX statement below.

DROP INDEX UQ_SSN ON Employee;

When we try to execute the above statement, we will receive an error stating

“An explicit DROP INDEX is not allowed on index ‘Employee.UQ_SSN’. It is being used for UNIQUE KEY constraint enforcement.”

Hence to drop this unique index UQ_SSN, we need to drop the unique key constraint directly using the below script.

ALTER TABLE Employee DROP CONSTRAINT UQ_SSN;

Executing the above command will drop both unique key constraint and corresponding Unique index as shown below.

Viewing the table structure in SSMS, we can see the below screen.

3. INDEX options like IGNORE_DUP_KEY, DROP_EXISTING, PAD_INDEX , and STATISTICS_NORECOMPUTE can be specified only on the unique indexes and not for the unique key constraints created on a table.

DROP Unique Key Constraint

To modify any unique key constraint, we need to first drop the unique key constraint and recreate it out with the required changes. We have already learned the script to drop the unique key constraint as shown below.

ALTER TABLE Employee DROP CONSTRAINT UQ_SSN;

Once dropped, we can recreate with necessary changes as required.

Disabling Unique Key Constraint

SQL Server provides the option to disable Constraints on a table using the below script.

ALTER TABLE Employee NOCHECK CONSTRAINT ALL;

However, disabling the unique key constraint using the above script won’t disable the unique key validation as it will disable the unique key constraint without disabling the unique key index created internally. To effectively disable a unique key constraint, we have to disable the unique index associated with the unique key constraint as shown below.

ALTER INDEX UQ_EmpNo ON Employee DISABLE;

Let’s try inserting a few duplicate records after disabling the unique Index to see whether it actually disables the unique key constraint as shown below.

INSERT INTO Employee (EmpNo, SSN) VALUES (1, '1234567890');
INSERT INTO Employee (EmpNo, SSN) VALUES (1, '2345678901');

Performing a SELECT on the Employee table yields the below result set.

To enable the unique Index, we need to execute the below script.

ALTER INDEX UQ_EmpNo ON Employee REBUILD;

Executing the above command to enable the unique Index, we will receive the below error as we have duplicate records in the table.

We would need to fix the duplicate values to enable the Unique index back. Ideally, there might not be any requirement to disable unique key constraints however we should be learning all these options to handle any situation in production.

Conclusion

Today, we have explored various constraints available in SQL Server like primary key, foreign key, unique key, and CHECK constraints. We have learned how to create, modify, delete or drop and disable unique key constraints. We have also understood the similarities and differences between primary key and unique key constraints and compared unique key constraints versus unique indexes as well. To speed up the SQL typing, you can use the Code Completion SSMS add-in SQL Complete. We will meet again with another interesting article.