Primary Key vs Foreign Key

Primary keys and foreign keys are the two halves of every relationship in a relational database. The confusion between them is the single most common modeling mistake, so it pays to get the distinction crisp.

Primary key: identity

• Identifies exactly one row — no duplicates, never NULL.
• Lives on the table it belongs to: users.id.
• Usually a surrogate (auto-increment integer or UUID) rather than a real-world value.
• One (possibly composite) per table, no more.

Foreign key: reference

• Points at another table's primary key: posts.user_id → users.id.
• Many can exist on one table — posts has FKs to users and to categories.
• Enforces referential integrity: the database rejects a post whose user_id does not exist.
• Optional by default; a NULL FK simply means 'no link'.

The same column, two roles

A column can be both at once. In a join table like enrollments, (student_id, class_id) is the composite primary key of the pair, and each part is also a foreign key to its own table. That is why join tables show both badges in a diagram.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,          -- PK: identity of the row
  email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,          -- PK again
  user_id INTEGER NOT NULL REFERENCES users(id)  -- FK: reference
);

In an ER diagram, the edge always runs from the referenced side (the parent's PK) to the referencing side (the child's FK). If you see an edge you do not understand, find which column carries the FK — that table is the child.

Choosing a primary key: surrogate vs natural

A natural key is an attribute that already exists in the domain and happens to be unique: an ISBN for a book, an ISO code for a country, a national ID number. A surrogate key is a value invented purely for the database, like a SERIAL integer or a UUID. Both are legitimate; they fail in different ways.

CREATE TABLE countries (
  iso_code CHAR(2) PRIMARY KEY,   -- 'FR', 'JP', 'BR' — stable by treaty
  name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
  isbn13 CHAR(13) PRIMARY KEY,    -- assigned by the publisher, immutable
  title VARCHAR(255) NOT NULL
);

Natural keys are fine when the value is stable, guaranteed unique, and never reused. The problem is that most real-world candidates fail at least one of those tests. Phone numbers get recycled, usernames can be changed, and email addresses are edited by users on a whim. If a primary key value ever has to change, every table referencing it must change too — a painful, lock-heavy migration. That is why most teams default to a surrogate id and put a UNIQUE constraint on the natural attribute instead.

What the foreign key actually enforces

Declaring a foreign key does two things. First, it validates inserts and updates: the database refuses a child row whose parent does not exist. Second, it decides what happens to children when a parent is deleted or its key changes, through the ON DELETE and ON UPDATE clauses.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT
);

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  body TEXT NOT NULL
);

CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  uploader_id INTEGER REFERENCES users(id) ON DELETE SET NULL
);

RESTRICT is the safe default: you cannot delete a customer who still has orders. CASCADE models ownership — comments belong to their post, so the post's deletion takes them along. SET NULL fits references that are convenient but not essential, like 'who uploaded this file'. Pick these behaviors per relationship, not globally; an accidental CASCADE has been known to empty half a schema in one DELETE.

Common mistakes

• Declaring the FK on the parent table. The foreign key always lives on the child — the table that references. users.post_id is almost always wrong; posts.user_id is right.
• Using email, username, or another mutable value as the primary key. The day the user changes it, every child row needs rewriting.
• Skipping the foreign key constraint 'for performance'. The JOIN still works without it, but nothing stops orphaned rows, and the diagram generated from the schema shows no relationship at all.
• Referencing a column that is not unique. A foreign key must point at a primary key or at a column with a UNIQUE constraint; anything else is rejected.
• Assuming a foreign key column is indexed. PostgreSQL creates an index for the primary key but not for the referencing column — JOINs and deletes on the parent get slow unless you index it yourself.
• Mixing types: an INTEGER FK pointing at a BIGINT PK, or SERIAL on one side and INTEGER on the other, fails at creation time in PostgreSQL. Match types exactly.

Best practices

• Use a surrogate primary key (BIGINT GENERATED ALWAYS AS IDENTITY or UUID) on every table, and add UNIQUE constraints for the natural identifiers.
• Declare every foreign key, even in early prototypes. Retrofitting them onto a table with millions of orphaned rows is a project of its own.
• Index every foreign key column. In PostgreSQL that means an explicit CREATE INDEX — the constraint alone does not do it.
• Name the constraint explicitly (CONSTRAINT orders_customer_id_fkey FOREIGN KEY ...) so error messages and migrations stay readable.
• Prefer RESTRICT over CASCADE unless the child genuinely cannot exist without the parent, and be suspicious of CASCADE chains longer than two hops.
• In the diagram, check that every edge corresponds to a declared FK — and conversely, that every FK shows up as an edge. A mismatch means the schema and the model disagree.

Can a foreign key be NULL?

Yes, unless the column is declared NOT NULL. A NULL foreign key means 'this row has no parent yet' — a comment posted before moderation assigns it to a thread, an employee with no assigned manager. If the relationship is mandatory, add NOT NULL and the database will enforce it alongside the reference itself.

Can a foreign key point at something other than the primary key?

It can point at any column (or column set) with a UNIQUE constraint. For example, orders.country_code may reference countries(iso_code) even though iso_code is not the PK of some other table design. Composite foreign keys work the same way: the child columns must match the referenced unique set in number, order, and type.

Do foreign keys slow down inserts?

Slightly — each insert checks the parent index. In practice the cost is negligible next to the bugs referential integrity prevents. The one place FK checks genuinely hurt is bulk loading; for large imports it is common to drop or defer the constraints, load, clean up, and re-add them. PostgreSQL also supports DEFERRABLE constraints that wait until the end of the transaction to validate.

Why does my diagram show a line between tables I never linked?

Because a foreign key exists somewhere you forgot about — often one created by an ORM migration or a JOIN table. Diagrams are derived from declared constraints, not from intent. Trace the edge to the FK column, and either fix the schema or accept that the relationship is real.