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.