T-SQL 101: 134 What is a Unique Constraint in SQL Server?

In the previous post, I talked about check constraints. Another type of constraint in SQL Server is a unique constraint.

What I want to specify is that a value in a column is unique i.e., no other row has that same value in that column. While a check of the values can be manually requested, the constraint is normally checked when SQL Server is inserting or updating the row.

It is also possible to specify a unique constraint at the table level, rather than at the column level. You might want a combination of columns to be unique. Single column unique constraints can be declared at either the column or the table level.

Broken SQL Server Implementation

One of the problems with unique constraints though, is that they’re not really properly implemented in SQL Server.

The way unique constraints should work is that they should only check that values are unique if there are values. If I had a unique constraint for a nullable column, and multiple rows don’t currently have a value, that should be ok. However, the way it’s implemented in SQL Server, unfortunately they treat NULL like it’s a value. So you’ll find that you can also only have one row that’s NULL.

That’s just not how unique constraints are meant to work. And I must admit, I do wish they would have a way to change that.

The current best workaround that I have found is to create a filtered unique index on the column(s) where the filter looks for rows that are not null.

Candidate Keys

Unique keys are not as common as you might suspect, particularly non-nullable ones. If you think how table are designed, a primary key is unique and not null. It’s not common for tables to have multiple keys that would satisfy this requirement. They are called candidate keys.

If you look at the table in the example, notice that ExternalCourseOrder might be a better primary key for the table than the surrogate key CourseRatingID. It’s unique, not null, and more meaningful.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-03-24