SQL UNIQUE Constraint

Summary: in this tutorial, you will learn about SQL unique constraint and how to ensure no duplicate values in a specific column.

Introduction to SQL UNIQUE constraint

The UNIQUE constraint ensures no duplicate values in a specific column, which is not a primary key.  A table can have only one PRIMARY KEY constraint, however it can have multiple UNIQUE constraints. Unlike the PRIMARY KEY constraint, you can enter a NULL value for a column that has UNIQUE constraint. However only one NULL value is allowed.

You use UNIQUE constraint for a column or a combination of columns that is not the primary key of a table.

Creating SQL UNIQUE constraint

You can create UNIQUE constraints using CREATE TABLE or ALTER TABLE statement. You can create UNIQUE constraint on one or more columns of a table.

Let’s take a look at some examples of creating UNIQUE constraints.

SQL UNIQUE constraint on 1 column example

Consider the following users table:

SQL UNIQUE Constraint - Users Table

It has a userid column as a primary key that uniquely defines each row in the table. However, user name must be unique to enable the system to distinguish between 2 users when they logged in.  To enforce uniqueness of the user name, you can create a UNIQUE constraint as follows:

If you try to enter a new row that would cause duplicate values in the username column, the database system will issue an error message.

SQL UNIQUE constraint for 2 columns example

The email of a user must be unique as well so that when the system send out any notification, the corresponding user will receive it. It also prevent one user with the same email address from creating multiple accounts in the system.

You can add a UNIQUE constraints to enforce this. To make it more convenient, SQL allows you to define a UNIQUE constraint for multiple columns as follows:

This ensures the pair of username and email value to be unique in the users table.

Notice that we named the UNIQUE constraints using CONSTRAINT keyword. We can use this name to remove the UNIQUE constraint later if we want.

To define a UNIQUE on multiple columns, we put a comma-separated columns list inside parenthesis that follows the UNIQUE keyword.

Add SQL UNIQUE constraint using SQL ALTER TABLE statement

You can add a UNIQUE constraint to an existing table using ALTER TABLE statement. For example, the following query add a UNIQUE constraint for two columns: username and email.

Removing SQL UNIQUE constraint

To remove an existing UNIQUE constraint, you also use the ALTER TABLE statement as follows:

For example, to remove the user_info UNIQUE constraint that we have created in the example above, we use the following command:

In this tutorial, we have shown you how to use SQL UNIQUE constraint to enforce the uniqueness of values on non-primary key columns.