customers has the primary key id. email is a candidate key too: two customers must never have the same email. UNIQUE makes the database enforce it.

UNIQUE on a column

CREATE TABLE customers (
  id    integer PRIMARY KEY,
  name  text NOT NULL,
  email text UNIQUE
);

An INSERT with an email that another row has fails:

ERROR: duplicate key value violates unique constraint "customers_email_key"
DETAIL: Key (email)=(ava.chen@example.com) already exists.

PostgreSQL names the constraint <table>_<column>_key.

UNIQUE and NULL

UNIQUE refuses two equal values. NULL = NULL is not true (Chapter 2), so NULLs are not equal to each other. A UNIQUE column accepts many NULLs.

To make a value required and unique, write both: email text NOT NULL UNIQUE.

UNIQUE on several columns

Customers vote for their favourite products. Each customer votes once for each product. One customer votes for many products, and one product gets many votes. Only the pair must be unique. Write the constraint after the columns, with the column list in parentheses:

CREATE TABLE votes (
  customer_id integer NOT NULL,
  product_id  integer NOT NULL,
  UNIQUE (customer_id, product_id)
);

MySQL also accepts many NULLs in a UNIQUE column. SQL Server accepts only one.