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
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:
Code language: PHP (php)
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) )
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:
Code language: PHP (php)
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) )
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)
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_name
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_info
In this tutorial, we have shown you how to use SQL UNIQUE constraint to enforce the uniqueness of values on non-primary key columns.