UNIQUE

Objectives

By the end of this lesson, you should be able to:

• Use UNIQUE to prevent duplicate values in a column
• Explain the difference between UNIQUE and PRIMARY KEY

💡 Why this matters: Some columns need to be distinct across every row, an email address, a username, without being the table’s actual identifier. UNIQUE enforces exactly that.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

Enforcing Distinct Values

email VARCHAR(150) UNIQUE NOT NULL

Every value in this column must differ from every other row’s value. A first insert with a given email succeeds:

INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Erin', 'Walsh', 'erin.walsh@example.com', 78000);

A second employee with the same email is rejected:

INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Sam', 'Ortiz', 'erin.walsh@example.com', 60000);

ERROR: duplicate key value violates unique constraint "employees_email_key"

UNIQUE vs. PRIMARY KEY

They sound similar, but serve different roles:

• A PRIMARY KEY is the one column (or set of columns) that identifies a row, a table has exactly one, and it can never be NULL.
• UNIQUE can be applied to any number of columns, and, unlike PRIMARY KEY, a UNIQUE column is still allowed to contain NULL (more than one row can have a NULL email, NULL is never considered equal to another NULL, even for uniqueness).

email in the employees table is a good example of UNIQUE without being the primary key, it needs to be distinct, but id is still what actually identifies each row.

Try It

1. Add a UNIQUE constraint to a username column, then write an INSERT that would violate it.
2. Explain, in your own words, why a table might have both a PRIMARY KEY and one or more UNIQUE columns.
3. Explain why two rows are both allowed to have NULL in a UNIQUE column, even though UNIQUE normally prevents duplicates.

Recap

• UNIQUE guarantees no two rows share the same value in that column.
• A table has one PRIMARY KEY, but can have several UNIQUE columns, and a UNIQUE column, unlike a primary key, can still contain NULL.

Next lesson: SERIAL and IDENTITY columns, two ways to make PostgreSQL generate a primary key’s value automatically.