SQL UNIQUE Constraint

Summary: in this tutorial, you will learn how to use the SQL UNIQUE constraint to ensure all values in a column or set of columns are distinct.

Introduction to SQL UNIQUE constraint #

In SQL, a UNIQUE constraint ensures that all values in a column or set of columns are unique within the same table. A UNIQUE constraint helps you maintain the data integrity by preventing duplicate values in the specified columns.

In practice, you’ll find UNIQUE constraints helpful for defining columns that require unique values like username and email.

Creating a UNIQUE constraint #

To create a unique constraint for a column, you use the following syntax:

CREATE TABLE table_name (
    column1 datatype UNIQUE,
    ...
);Code language: SQL (Structured Query Language) (sql)

In this syntax, you use the UNIQUE keyword to create a unique constraint for the column1 when creating the table. This UNIQUE constraint is a column constraint because we define it in the column definition of the table.

If a UNIQUE constraint includes more than one column, you can define a UNIQUE constraint as a table constraint:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    UNIQUE (column1, column2)
);Code language: SQL (Structured Query Language) (sql)

In this syntax, you provide a comma-separated list of columns in within the parentheses followed by the UNIQUE keyword that appears at the end of the columns list.

First, create a table called headhunters that store the information about headhunters:

CREATE TABLE headhunters (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);Code language: SQL (Structured Query Language) (sql)

Try it

In the headhunters table, we use a UNIQUE constraint for the email column to ensure that the email column will not have any duplicate values.

Second, insert a new row into the headhunters table:

INSERT INTO
  headhunters (first_name, last_name, email)
VALUES
  ('John', 'Doe', '[email protected]');Code language: SQL (Structured Query Language) (sql)

Try it

Third, attempt to insert a new row with an email that already exists in the email column:

INSERT INTO
  headhunters (first_name, last_name, email)
VALUES
  ('Jane', 'Doe', '[email protected]');Code language: SQL (Structured Query Language) (sql)

The database system issued an error indicating that the email is duplicated and rejected the insert:

ERROR:  duplicate key value violates unique constraint "headhunters_email_key"Code language: SQL (Structured Query Language) (sql)

UNIQUE constraint name #

When defining a unique constraint, you can optionally assign it a name using the CONSTRAINT clause:

CONSTRAINT constraint_name
UNIQUE (column1, column2, ...);Code language: SQL (Structured Query Language) (sql)

For example, the following statement creates a table called users that stores the user information:

CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username VARCHAR(25) NOT NULL,
  password TEXT NOT NULL,
  phone VARCHAR(20),
  CONSTRAINT unique_username UNIQUE (username)
);Code language: SQL (Structured Query Language) (sql)

Try it

In this example, we create a unique constraint with the name unique_username to ensure that the username column has no duplicate values.

If you don’t provide a name for the UNIQUE constraint, the database system will generate a default name for it. The generated unique constraint name may vary depending on the database systems.

The following table illustrates the default names for the unique constraints in different database systems:

Adding UNIQUE constraints to existing tables #

To add a unique constraint to an existing table, you can use the ALTER TABLE ... ADD CONSTRAINT statement:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
UNIQUE (colum1, column2, ...);Code language: SQL (Structured Query Language) (sql)

If the table has data, the values in columns (column1, column2, …) of the unique constraint must be distinct or you’ll encounter an error.

For example, the following statement uses the ALTER TABLE ... ADD CONSTRAINT statement to add a unique constraint to the users table:

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

Try it

If you want to add a new column with a UNIQUE constraint, you use the ALTER TABLE ... ADD column statement:

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

For example, the following statement adds the employee_id column with the UNIQUE constraint to the users table.

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

Try it

This unique constraint ensures that no two employees share the same user account.

Removing UNIQUE constraints #

To remove a UNIQUE constraint from a table, you use the ALTER TABLE .. DROP CONSTRAINT statement with the following syntax:

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

In this syntax:

• First, specify the table name you want to remove the unique constraint.
• Second, provide the unique constraint name you want to drop in the DROP CONSTRAINT clause.

For example, the following statement drops the unique_phone constraint from the users table:

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

UNIQUE vs. PRIMARY KEY constraints #

A table can have one and only one PRIMARY KEY constraint but can have multiple UNIQUE constraints. Additionally, a PRIMARY KEY constraint does not allow NULL whereas a UNIQUE constraint allows NULL.

The following table compares the PRIMARY KEY and UNIQUE constraints:

Summary #

• Use the UNIQUE constraint to ensure a column of a set of columns has distinct values.

Quiz #

Databases #

• PostgreSQL UNIQUE constraint
• MySQL UNIQUE constraint
• MariaDB UNIQUE constraint
• Oracle UNIQUE constraint
• Db2 UNIQUE constraint
• SQLite UNIQUE constraint