SQL Primary Key vs Foreign Key: Differences and Examples

Open any database and you will see two kinds of columns doing quiet but critical work. One decides which row is which. The other decides which rows belong together. The primary key vs foreign key question is really about those two jobs.

Mix them up and you get duplicate customers, orders that point to nobody and reports that no one trusts. Get them right and the database protects your data for you.

Below you will find short definitions, a side-by-side table and a small script you can run in any SQL tool. If joins are new to you, read SQL joins explained next, because keys are what make joins possible.

Primary key vs foreign key, part one: the primary key

A primary key is a column, or a set of columns, whose value is different for every row in a table. It is how the database, and your code, finds exactly one row. According to the PostgreSQL constraints documentation, a primary key is the combination of a unique constraint and a not-null constraint, and the database creates a unique index for it automatically.

• Values must be unique.
• Values cannot be NULL.
• A table can have only one primary key, but it may span more than one column (a composite key).
• Good keys rarely change. An auto-generated id is the usual choice.

Primary key vs foreign key, part two: the foreign key

A foreign key is a column in one table (the child) whose values must match a key in another table (the parent). It is the database’s way of saying “this order must belong to a real customer”. The rule is called referential integrity.

• Values may repeat. Many orders can belong to one customer.
• Values may be NULL, which means “no parent yet”, unless you add NOT NULL.
• A table can have several foreign keys.
• It normally points to a primary key, though most databases also allow a column with a unique constraint.

Primary key vs foreign key: comparison table

A runnable primary key vs foreign key example

This script works in SQLite, PostgreSQL and MySQL with minor changes. We model customers and their orders.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email       VARCHAR(120) NOT NULL UNIQUE
);

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total       DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

INSERT INTO customers VALUES (1, 'asha@example.com'), (2, 'ben@example.com');
INSERT INTO orders VALUES (100, 1, 49.90), (101, 1, 12.00), (102, 2, 30.00);

Here customer_id in customers is the primary key. The same name in orders is the foreign key. Now try to break the rules:

-- duplicate primary key: rejected
INSERT INTO customers VALUES (1, 'copy@example.com');

-- orphan row, no customer 99: rejected
INSERT INTO orders VALUES (103, 99, 5.00);

-- valid join using the key pair
SELECT c.email, o.order_id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

What happens when the parent row is deleted?

You choose the behaviour when you create the foreign key. The standard options are:

FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
  ON DELETE RESTRICT ON UPDATE CASCADE

Use CASCADE with care. It is convenient for child data that has no meaning alone, such as order lines, and dangerous for financial history.

Indexing: the part beginners miss

Performance is where the primary key vs foreign key gap shows most clearly.

The primary key gets an index automatically. Foreign key columns do not always. PostgreSQL does not create one for the referencing column, so joins and parent deletes can become slow on large tables unless you add it yourself. MySQL’s InnoDB requires an index on the foreign key columns and creates one if missing. Check your own database’s documentation before you assume.

CREATE INDEX idx_orders_customer ON orders (customer_id);

For more on grouping the joined rows afterwards, see SQL GROUP BY and HAVING explained.

How testers check keys

Testers meet the primary key vs foreign key rules every time they validate data, because broken keys cause the nastiest bugs.

Constraints are business rules, so they deserve tests. A short checklist for any new table:

• Insert a duplicate primary key and expect an error.
• Insert NULL into the primary key and expect an error.
• Insert a child row with a missing parent and expect an error.
• Delete a parent that has children and confirm the rule you chose (block, cascade or set null).
• Run an orphan check on existing data:

SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

An empty result means no orphans. Our SQL tutorial for beginners covers the statements used here from scratch.

Common primary key vs foreign key mistakes

• Using a mutable value as the primary key, such as an email address that users can change.
• Skipping foreign keys “for speed” and relying on application code. One bug later you have orphan rows.
• Forgetting the index on the foreign key column.
• Cascading deletes everywhere, which can wipe out far more data than intended.
• Assuming SQLite enforces foreign keys without enabling the pragma.

Final thoughts

The primary key vs foreign key rule of thumb is simple: the primary key says who a row is, and the foreign key says who it belongs to. Define both when you create the tables, pick delete rules on purpose, index the foreign key and write tests for the failure cases.

Frequently asked questions

Keep learning