Database DesignThinking in entitiesLesson 18 / 69

Primary keys

Fill-gap20 min

Primary keys

Every row needs an unambiguous name: the primary key. The database enforces two promises about it — unique, and never null.

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email varchar NOT NULL UNIQUE
);

You can also key on something natural (email above is a candidate key), but a meaningless id wins in practice: emails change, ids never do. When you draw diagrams, underline the primary key — that underline is the convention every engineer reads.

Exercise

Design keys for a songs table. Argue for your choice: natural key (title?) vs surrogate id — what breaks with each?