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 duplicated. For example, duplicate emails in the employee's table are not acceptable.

Since the email column is not 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 in 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 that 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

	PRIMARY KEY constraint	UNIQUE constraint
The number of constraints	One	Many
NULL values	Do not allow	Allow

Creating UNIQUE constraints

Typically, you create a UNIQUE constraint when creating the table. The following CREATE TABLE statement defines the user's table with the username column as unique.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);

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)
);

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

Adding UNIQUE constraints to an 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 that participates in the UNIQUE constraint must contain unique values.

Suppose the user's 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);

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;

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;

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;

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

ALTER TABLE users
DROP CONSTRAINT uc_username;

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.