SQL UNIQUE Constraint Example

The constraint UNIQUE defines one or more columns to contain unique values.

  • The NULL can be one of the unique values of a column.
  • No two rows can have the same value in a column defined as UNIQUE.
  • Multiple UNIQUE constraints can be used for multiple columns of a table, without any restrictions.

In the below example, the column phone is defined as UNIQUE.

Run this on IDE

CREATE TABLE persons (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    birth_date DATE,
    phone VARCHAR(15) NOT NULL UNIQUE
);

In case the table already exists, it returns an error message, which can be avoided by including the keyword IF NOT EXISTS as shown below.

CREATE TABLE IF NOT EXISTS persons (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    birth_date DATE,
    phone VARCHAR(15) NOT NULL UNIQUE
);

Overall

We now know when and how to use the UNIQUE constraint.

Related Links