19 Primary Keys and UNIQUE

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 UNIQUEs per table, and they may include nullable columns.

What you’ll 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

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

To run:

$ 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

• 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.

Downloads

SQL file: primary-keys-and-unique.sql · All lesson files · Setup guide