SQL UNIQUE Constraint

Summary: in this tutorial, you will learn how to enforce the uniqueness of values in a column or a set of columns using SQL UNIQUE constraint.

What is SQL UNIQUE constraint

Sometimes, you want to make sure that the values in a column or a set of columns are not duplicate. For example, duplicate emails in the employees table are not acceptable.

Since the email column is not the part of the primary key, the only way to prevent duplicate values in the email column is to use a UNIQUE constraint.

By definition, an SQL UNIQUE constraint defines a rule that prevents duplicate values stored in specific columns that do not participate a primary key.

UNIQUE vs. PRIMARY KEY constraints

You can have at most one PRIMARY KEY constraint whereas you can have multiple UNIQUE constraints in a table. In case you have multiple UNIQUE constraints in a table, all UNIQUE constraints must have a different set of columns.

Different from the PRIMARY KEY constraint, the UNIQUE constraint allows NULL values. It depends on the RDBMS to consider NULL values are unique or not.

For example, MySQL treats the NULL values as distinct values, therefore, you can store multiple NULL values in the columns participated in the UNIQUE constraint. However, it is not the case for Microsoft SQL Server or Oracle Database.

The following table illustrates the differences between UNIQUE constraint and PRIMARY KEY constraint:

Not Allowed

Creating UNIQUE constraints

Typically, you create UNIQUE constraint when creating the table. The following CREATE TABLE statement defines the users table with the username column is unique.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

To create a UNIQUE constraint for a column, you need to add the UNIQUE keyword in the column definition. In this case, we created the UNIQUE constraint as the column constraint.

If you insert or update the value that is the same as the one which already exists in the username column, the RDBMS will reject the change and return an error.

The following statement is equivalent to the above statement with the UNIQUE constraint created using the table constraint syntax.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    CONSTRAINT uc_username UNIQUE (username)
);Code language: SQL (Structured Query Language) (sql)

In this case, you put the CONSTRAINT clause at the end of the CREATE TABLE statement.

Adding UNIQUE constraints to existing table

In case the table already exists, you can add a UNIQUE constraint for columns with the prerequisite that the column or the combination of columns which participates in the UNIQUE constraint must contain unique values.

Suppose the users table was created without the UNIQUE constraint defined for the username column. To add the UNIQUE constraint to the username column, you use the ALTER TABLE statement as follows:

ALTER TABLE users
ADD CONSTRAINT uc_username UNIQUE(username);Code language: SQL (Structured Query Language) (sql)

If you want to add a new column and create a UNIQUE constraint for it, you use the following form of the ALTER TABLE statement.

ALTER TABLE users
ADD new_column data_type UNIQUE;Code language: SQL (Structured Query Language) (sql)

For example, the following statement adds the email column with the UNIQUE constraint to the user table.

ALTER TABLE users
ADD email VARCHAR(255) UNIQUE;Code language: SQL (Structured Query Language) (sql)

Removing UNIQUE constraint

To remove a UNIQUE constraint, you use the ALTER TABLE statement as follows:

ALTER TABLE table_name
DROP CONSTRAINT unique_constraint_name;Code language: SQL (Structured Query Language) (sql)

For example, to remove the uc_username unique constraint in the users table, you use the following statement.

ALTER TABLE users
DROP CONSTRAINT uc_username;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about the UNIQUE constraint and how to apply it to enforce the uniqueness of values in a column or a set of columns.