CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 3 of 5 · 1 min read

Primary key and composite key

Choosing one candidate key to identify rows, and building a key from more than one column.

Think of a library card number. A library could identify a member by name and birth date together, but it instead issues one card number, chosen specifically to be the one official identifier everything else refers to.

In plain words: a primary key is the one candidate key a table's designer picks to be its official identifier. Every other table that needs to reference a row does so through the primary key, not through some other candidate key that happens to also be unique. A composite key is a primary key (or any key) made of more than one column, used when no single column is unique on its own.

A worked example. book_genres (book_id, genre) records that a book belongs to a genre, and a book can have more than one genre. Neither book_id alone nor genre alone is unique, since the same book appears in multiple rows and the same genre applies to many books. The pair (book_id, genre) together is unique, so the primary key is the composite key {book_id, genre}.

The key lines.

CREATE TABLE book_genres (
    book_id INTEGER NOT NULL REFERENCES books(book_id),
    genre   TEXT NOT NULL,
    PRIMARY KEY (book_id, genre)
);

Primary key, composite key

A primary key is the single candidate key chosen as a table's official row identifier, used by every foreign key that references the table. A composite key is any key, primary or otherwise, built from more than one column because no single column is unique alone.

*Example:* `{book_id, genre}` is a composite primary key for `book_genres`, since no single column of the two is unique by itself.