# Primary Keys and UNIQUE

> Choose primary keys and unique constraints, including how they treat NULL.

---

LLMS index: [llms.txt](/en/llms.txt)

---

A *primary key* uniquely identifies a row. Every table should have one — it gives you a stable handle into the row from indexes, joins, foreign keys, and application code. A table can have at most one primary key (which may span multiple columns). `UNIQUE` is the more general "no duplicates here" constraint; you can have several `UNIQUE`s per table, and they may include nullable columns.

## What you'll learn {#learn}

- The difference between `PRIMARY KEY` and `UNIQUE`
- Single-column vs composite (multi-column) primary keys
- Surrogate keys (a generated `id`) vs natural keys (e.g. an email)
- How `NULL` is treated by `UNIQUE` (each NULL is distinct, unless `NULLS NOT DISTINCT`)
- The index that a PK / UNIQUE automatically creates

## Example {#example}

```sql {num="19.1" caption="Primary Keys and UNIQUE"}
DROP TABLE IF EXISTS users_example;

-- Inline primary key + unique
CREATE TABLE users_example (
  id    integer PRIMARY KEY,
  email text UNIQUE NOT NULL,
  name  text NOT NULL
);

INSERT INTO users_example (id, email, name) VALUES
  (1, 'alice@example.com', 'Alice'),
  (2, 'bob@example.com',   'Bob');

SELECT * FROM users_example ORDER BY id;

-- Composite primary key (across two columns)
DROP TABLE IF EXISTS enrollments_example;
CREATE TABLE enrollments_example (
  student_id integer,
  course_id  integer,
  enrolled_at timestamptz NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (student_id, course_id)
);

INSERT INTO enrollments_example (student_id, course_id) VALUES (1, 100), (1, 200), (2, 100);
SELECT * FROM enrollments_example ORDER BY student_id, course_id;

-- See indexes that the constraints created
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename IN ('users_example', 'enrollments_example')
ORDER BY indexname;
```

A primary key combines `NOT NULL` and `UNIQUE` and creates a B-tree index. PostgreSQL uses the primary key index for joins, for `WHERE id = ?` lookups, and as the natural row identifier in foreign keys. Every well-designed table has one.

`UNIQUE` is the more flexible cousin. You can have several uniques per table — `UNIQUE (email)`, `UNIQUE (handle)`. A `UNIQUE` constraint allows NULL values; under the standard, NULL is *distinct from every other NULL*, so multiple rows with `NULL` in the same unique column are allowed. From PostgreSQL 15, you can override this with `UNIQUE NULLS NOT DISTINCT` if you want NULL to count as a duplicate.

Composite keys (`PRIMARY KEY (a, b)`) are common for *junction tables* representing many-to-many relationships (a student is enrolled in many courses; a course has many students; the pair is unique).

## Run the example {#run}

To run:

```text
$ psql -f source/primary-keys-and-unique.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 2
 id |       email       | name
----+-------------------+-------
  1 | alice@example.com | Alice
  2 | bob@example.com   | Bob
(2 rows)
 ...
```

## Common pitfalls {#pitfalls}

- A `UNIQUE` column with NULLs allowed: two NULLs are *not* a duplicate by default. If your business rule says "the column is optional but if set, must be unique," that is the default — but if your rule says "treat NULL as equal," use `UNIQUE NULLS NOT DISTINCT` (PG 15+). A partial unique index with `WHERE col IS NOT NULL` still allows multiple NULLs and is not a substitute.
- Choosing a natural key like `email` as the primary key seems clean — until the user changes their email. Surrogate keys (`id` + `UNIQUE (email)`) are usually safer.
- Composite primary keys propagate into foreign keys; a child table that references a composite PK must duplicate both columns. Often a simpler design is to give the parent a surrogate `id`.
- `PRIMARY KEY` and `UNIQUE` each create an index automatically; do not create another B-tree index on the same column.

> [!TIP] Tip
> Use `bigint` (or `id bigint GENERATED ALWAYS AS IDENTITY`) for the PK on any table you expect to grow past a few million rows. Going from `integer` to `bigint` later is painful — change the type up front when in doubt.

> [!NOTE] Try it
> Try inserting a duplicate: `INSERT INTO users_example (id, email, name) VALUES (1, 'x@example.com', 'X');` — the PK rejects it. Then `INSERT ... VALUES (3, 'alice@example.com', 'Alice2');` — the UNIQUE on email rejects it. Then run `\d users_example` in psql to see the indexes and constraints.

## Downloads {#downloads}

SQL file: [primary-keys-and-unique.sql](/examples/source/primary-keys-and-unique.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)