SQL UNIQUE

UNIQUE guarantees no two rows share the column's value. The database enforces it with an index.

Single-column unique

CREATE TABLE customers (
  id    INT PRIMARY KEY,
  email VARCHAR(120) UNIQUE
);

Composite unique

Unique applied to several columns means the combination must be unique, but each individual column can repeat:

CREATE TABLE memberships (
  user_id INT,
  team_id INT,
  joined_at DATETIME,
  CONSTRAINT uq_user_team UNIQUE (user_id, team_id)
);

Add or drop later

ALTER TABLE customers
ADD CONSTRAINT uq_email UNIQUE (email);

ALTER TABLE customers
DROP CONSTRAINT uq_email;

UNIQUE and NULL

PRIMARY KEY vs UNIQUE

• A table can have one PRIMARY KEY but many UNIQUEs.
• PRIMARY KEY implies NOT NULL; UNIQUE does not.

Example

ALTER TABLE customers
ADD CONSTRAINT uq_email UNIQUE (email);

Exercise

Test yourself