What is a UNIQUE Constraint?

A UNIQUE constraint is a rule we apply to one or more columns in a database table to ensure that the values in those columns are unique across all rows. This means that no two rows in the table can have the same value(s) in the column(s) where we’ve applied the UNIQUE constraint. It’s an essential tool for maintaining data integrity and preventing duplicate entries in our database.

When we create a UNIQUE constraint, we’re telling our database management system (DBMS) to enforce uniqueness on the specified column(s). If we try to insert or update a row that would violate this uniqueness, the DBMS will reject the operation and return an error.

Let’s look at some examples of how we can implement UNIQUE constraints:

Single Column `UNIQUE` Constraint

Suppose we have a table for employees, and we want to ensure that each employee has a unique email address:

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.

Another way to do this is to put the UNIQUE definition at the end of the column list.:

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

In this case we provided a name for the constraint; UQ_Employees_Email.

Multi-Column `UNIQUE` Constraint

Now, let’s say we have a table for course enrollments, and we want to ensure that a student can’t enroll in the same course more than once:

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. 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.

Adding a `UNIQUE` Constraint to Existing Columns

We can use the the ALTER TABLE statement to add a UNIQUE constraint to existing columns:

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

That code applies a UNIQUE constraint to the two columns listed.

Existing Duplicate Values

Typically, if the table already contains duplicate values for the relevant column(s), then an error is returned and the constraint is not created. It may be possible to work around this issue, but you would need to be sure that this is what you want to do.

One option is to create a filtered index, which is where we add a UNIQUE index, but filter out certain rows.

For example, the following code can be used to add a UNIQUE index, but only for new records:

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

That assumes that the EmployeeId column increments sequentially, and currently has 500 rows (or at least, that any duplicate columns are 500 or less, and that the next value inserted will be greater than 500).

If there are just a handful of duplicates scattered throughout many rows, you could try specifying them explicitly in the query.

NULL Values

Some systems may allow multiple NULL values, while others only allow a single NULL value. Others might not allow any. If you don’t want any NULL values, then you can always use a NOT NULL constraint (in addition to the UNIQUE constraint).

If you’re applying a UNIQUE constraint to an existing table that contains NULL values, you might want to see if those values should be something else. If they should remain NULL, then an option is to apply a filtered index like we did in the previous example, except this time you’d filter out NULLs on the column that you’re applying the UNIQUE index to:

CREATE UNIQUE INDEX UX_Employees_Email 
ON Employees(Email) 
WHERE Email IS NOT NULL;

In this case, any rows that have a NULL email will be excluded from the UNIQUE index.

Dropping a `UNIQUE` Constraint

We can drop UNIQUE constraints with the ALTER TABLE statement:

ALTER TABLE Employees   
DROP CONSTRAINT UQ_Employees_Email;

However, if we implemented it as a UNIQUE index, then we would use the DROP INDEX statement:

DROP INDEX UX_Employees_Email
ON Employees;

Compatibility

While the syntax for creating UNIQUE constraints is pretty much standard across most RDBMSs, there can be slight variations in how they work and the supported syntax. For that reason, it’s always a good idea to consult your DBMS’s documentation before implementing UNIQUE constraints across your databases.

`UNIQUE` vs Primary Key

All of the above assumes that you’re not using the UNIQUE index on the primary key column(s). Primary keys, by definition, are unique. They don’t allow duplicate values, and so they’ll always enforce uniqueness.

See What is a Primary Key? and What is a Composite Primary Key? for more information about primary keys.

Single Column UNIQUE Constraint

Multi-Column UNIQUE Constraint

Adding a UNIQUE Constraint to Existing Columns