SQL UNIQUE Constraint

Summary: in this tutorial, you will learn about SQL unique constraints 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 a UNIQUE constraint. However, only one NULL value is allowed.

You use a 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 a 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:

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

CREATE TABLE users (
  userid int(11) NOT NULL AUTO_INCREMENT,
  username varchar(25) DEFAULT NULL,
  password varchar(25) DEFAULT NULL,
  firstname varchar(45) NOT NULL,
  lastname varchar(45) NOT NULL,
  created date DEFAULT NULL,
  email varchar(255) DEFAULT NULL,
  PRIMARY KEY (userid), 
  UNIQUE(username)
)Code language: SQL (Structured Query Language) (sql)

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 sends out any notification, the corresponding user will receive it. It also prevents one user with the same email address from creating multiple accounts in the system.

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

CREATE TABLE users (
  userid int(11) NOT NULL AUTO_INCREMENT,
  username varchar(25) DEFAULT NULL,
  password varchar(25) DEFAULT NULL,
  firstname varchar(45) NOT NULL,
  lastname varchar(45) NOT NULL,
  created date DEFAULT NULL,
  email varchar(255) DEFAULT NULL,
  PRIMARY KEY (userid), 
  CONSTRAINT user_info UNIQUE(username,email)
)Code language: SQL (Structured Query Language) (sql)

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 adds a UNIQUE constraint for two columns: username and email.

ALTER TABLE users
ADD CONSTRAINT user_info UNIQUE (username, email)Code language: SQL (Structured Query Language) (sql)

Removing SQL UNIQUE constraint

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

ALTER TABLE table_name
DROP CONSTRAINT constraint_nameCode language: SQL (Structured Query Language) (sql)

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

ALTER TABLE users
DROP CONSTRAINT user_infoCode language: SQL (Structured Query Language) (sql)

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