UNIQUE Constraint

What is UNIQUE?

UNIQUE means "no duplicates allowed". Each value in the column must be different.

Simple rule: No two rows can have the same value in a UNIQUE column.

How UNIQUE Works

Basic Syntax

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100) UNIQUE
);

Example 1: Create Table with UNIQUE

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100) UNIQUE
);

Example 2: Insert Unique Values

INSERT INTO users (username, email) VALUES ('john', 'john@email.com');
INSERT INTO users (username, email) VALUES ('mary', 'mary@email.com');
-- This fails: INSERT INTO users (username, email) VALUES ('john', 'new@email.com');

Example 3: Query Users

SELECT * FROM users WHERE username = 'john';

UNIQUE vs Primary Key

| Feature | Primary Key | UNIQUE | |---------|-------------|--------| | Duplicates | No | No | | NULL allowed | No | Yes | | Per table | Only 1 | Multiple |

Try It Below

Use the playground to practice:

SELECT * FROM users;
SELECT * FROM users WHERE username = 'john';

What Comes Next

Next: Learn NOT NULL to ensure required fields always have values.