Primary keys and foreign keys: linking tables safely

In short: A primary key uniquely identifies each row of a table; a foreign key points to another table’s primary key and so prevents dangling references.

In more detail: Relational databases store data in separate tables (customers, orders). To know which order belongs to which customer, every row needs a unique identifier (primary key) and the linking table a reference to it (foreign key).

In Depth

Primary key (PRIMARY KEY)

It is unique and never empty (NOT NULL). Typical choices are a sequential number (SERIAL/IDENTITY) or a UUID. Surrogate keys never change. Natural keys (email, tax number) can change or repeat and are therefore risky. A primary key can span several columns (composite key), for example in link tables. The database automatically creates an index for it.

Foreign key (FOREIGN KEY)

Example: orders.customer_id REFERENCES customers(id). The database rejects rows whose customer_id does not exist (referential integrity). When the parent row is deleted or changed you define the behaviour: RESTRICT/NO ACTION (forbid), CASCADE (delete along), SET NULL (clear the reference). CASCADE is convenient but can delete unexpectedly many rows.

Practice and pitfalls

• Index foreign key columns yourself (PostgreSQL does not do it automatically); otherwise joins and deletions get slow.
• Relationship types: 1:n (foreign key in the n table), n:m (separate link table with two foreign keys).
• UUID or number? Numbers are compact and fast, UUIDs can be generated in a distributed way without coordination and reveal nothing about record counts.
• For bulk imports do not disable foreign keys; load data in the right order.
• Queries also work without foreign keys, but data garbage creeps in.

Exam view

Keys belong to the relational model and normalisation. Remember: primary key = unique + not NULL; foreign key = reference, ensures integrity. In an ER model, relationships are implemented with foreign keys.

See also: Joins, ER model, Normal forms