4 Ways to Create a UNIQUE Constraint in SQL Server

Posted on by Ian

A UNIQUE constraint is a rule that we can apply to one or more columns in a database table to ensure that the values in those columns are unique across all rows.

In SQL Server we have a few options when it comes to creating a UNIQUE constraint. But it’s usually done when we create the table or alter it. That is, we include the constraint code in the CREATE TABLE statement or the ALTER TABLE statement.

We can also create a UNIQUE index, which can be used in place of the previous methods, but can have the same effect.

Let’s check out several ways to create a UNIQUE constraint in SQL Server.

Option 1: Include it in the Column Definition

The most concise way to create a UNIQUE constraint when creating a table is to include it within the column definition:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) UNIQUE
);

In this example, the UNIQUE constraint is applied to the Email column. This means we can’t have two employees with the same email address.

We can also use more verbose code if we want to be more explicit:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) CONSTRAINT UQ_Employees_Email UNIQUE
);

In this case we explicitly stated CONSTRAINT and we also provided a name for the constraint (we named it UQ_Employees_Email). If we don’t name our constraints, SQL Server will name them for us. So in the previous code example SQL Server provided a name for our constraint.

This option works well if the constraint only needs to be applied to a single column. If we need to apply it across multiple columns, we can use the method below.

Option 2: Put it After All Column Definitions

We can also define our UNIQUE constraints at the end of the column definitions. This is true whether it’s applied to a single column or multiple columns.

Here’s how we can rewrite the previous example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    CONSTRAINT UQ_Employees_Email UNIQUE(Email)
);

So we applied the UNIQUE constraint to the same column.

As mentioned, this option can be used to create a UNIQUE constraint on multiple columns.

Here’s an example:

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    CONSTRAINT UQ_StudentCourse UNIQUE (StudentID, CourseID)
);

Here, we’ve created a multi-column UNIQUE constraint on the combination of StudentID and CourseID.

For example, we could have two rows with say, 10 in the StudentID column, but those two rows can’t share the same CourseId value. Likewise with the CourseID column. Multiple rows can share the same CourseID, as long as they don’t also share the same StudentID.

This allows a student to enroll in multiple courses and a course to have multiple students, but prevents a student from enrolling in the same course twice.

Option 3: Add it to an Existing Column

We can add UNIQUE constraints to existing columns in existing tables. We can do this with the ALTER TABLE statement:

ALTER TABLE Enrollments
ADD CONSTRAINT UQ_StudentCourse UNIQUE (StudentID, CourseID);

This is the same table as in the previous example, except that this assumes that we hadn’t created the UNIQUE constraint when we created the table.

Option 4: Create a UNIQUE Index

When we try to add a UNIQUE constraint to a table that already contains duplicate data, the operation fails and SQL Server returns an error. While this should be seen as a good thing, there may be times that we want to override this behavior. For example, we might be OK with keeping the existing duplicates, as long as no new duplicates are entered in the future.

This issue can be dealt with by implementing a UNIQUE index. In particular, a filtered UNIQUE index.

When we create a UNIQUE constraint, SQL Server creates a UNIQUE index to enforce the uniqueness of that constraint. So it’s the index that enforces it. Armed with that knowledge, we can create our own UNIQUE index and filter it to just the rows that we’re interested in.

For example:

CREATE UNIQUE INDEX UX_Employees_Email 
ON Employees(Email) 
WHERE EmployeeID > 500;

This adds a UNIQUE index, but only for rows greater than 500.

This example implies that the table currently has 500 rows and some (or all) of those rows contain duplicate email addresses. The UNIQUE index will therefore only be implemented on new rows that are inserted into the table – not existing rows.

The WHERE condition can (and probably should) be more granular than this. We could explicitly exclude the duplicate rows in our WHERE condition if required.

One possible challenge we might encounter is that filtered indexes don’t support the full range of operators that other statements support. For example, we can use the IN operator but we can’t use NOT IN. So we may encounter some issues there, but there’s often a workaround, even if it means a lot more code. Either way, at least filtered UNIQUE indexes are an option for the toolbox.