UNIQUE Constraint

The UNIQUE constraint makes sure that no two rows have the same value in a column (or in a group of columns).

Unlike PRIMARY KEY, a UNIQUE column can have NULL values. Only the values that are not NULL must be different.


Syntax

CREATE TABLE table_name (
  column_name datatype UNIQUE
);

For a combination of columns:

CREATE TABLE table_name (
  col1 datatype,
  col2 datatype,
  UNIQUE (col1, col2)
);

Or for a table that already exists:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

Example

CREATE TABLE users (
  user_id INT,
  email VARCHAR(100) UNIQUE
);

INSERT INTO users VALUES (1, '[email protected]');
INSERT INTO users VALUES (2, '[email protected]');

Output:

CREATE TABLE
INSERT 0 1
INSERT 0 1

Now try to add another user with Alice's email:

INSERT INTO users VALUES (3, '[email protected]');

Output:

ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

The insert failed, because [email protected] is already used. PostgreSQL named the constraint users_email_key, from the table and column names.


When to Use UNIQUE?

  • When a value must never appear twice.
  • For things like email addresses, usernames and phone numbers.

Next, you will learn PRIMARY KEY, which identifies each row in a table.

Answer the question below to check how UNIQUE works.