Primary Key vs Foreign Key: What's the Difference?

Two kinds of "key" hold a relational database together. A primary key gives every row a unique identity. A foreign key links a row to a row in another table. Get these right and your data stays consistent; skip them and you end up with orders belonging to customers who don't exist.

Primary key: the row's ID

A primary key is a column (or set of columns) that uniquely identifies each row in a table. No two rows can share it, and it can't be empty.

CREATE TABLE customers (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  email text NOT NULL UNIQUE
);

Here id is the primary key; the database generates 1, 2, 3… automatically. Every customer can now be referred to unambiguously as "customer 42".

Good primary keys:

• never change (don't use an email address — people change them),
• are meaningless — just an identifier, not real-world data,
• are usually an auto-incrementing number or a UUID. (UUID vs auto-increment compares them.)

Each table has exactly one primary key. The database automatically indexes it, so looking up a row by its primary key is fast.

Foreign key: the link to another table

A foreign key is a column in one table that refers to the primary key of another table:

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  total       numeric(10, 2) NOT NULL
);

orders.customer_id is a foreign key pointing at customers.id. It says "this order belongs to that customer" — and the database enforces it:

• You can't create an order for customer 999 if customer 999 doesn't exist.
• You can't delete a customer who still has orders (by default).

That enforcement is called referential integrity, and it's the whole point. It's how you use joins confidently: every customer_id is guaranteed to match a real customer.

Side by side

	Primary key	Foreign key
Purpose	Identify each row uniquely	Link to a row in another table
Unique?	Yes	No — many orders can share one customer
Can be NULL?	No	Yes, unless you add NOT NULL
Per table	Exactly one	Any number
Indexed automatically?	Yes	Not in Postgres — add one yourself

That last row matters: Postgres doesn't automatically index foreign key columns. Add an index on orders.customer_id, or queries like "all orders for this customer" — and deleting customers — get slow as the table grows. See database indexes.

What happens when you delete? ON DELETE

You choose what happens to orders when their customer is deleted:

customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE CASCADE

Option	Effect
(default) / RESTRICT	Refuse to delete the customer while orders exist
CASCADE	Delete the customer's orders too
SET NULL	Keep the orders, set customer_id to NULL

Be careful with CASCADE. It's convenient for things that truly belong to a parent (a post's comments), but dangerous for valuable data: deleting one customer silently deletes their entire order history. For important records, prefer the default and handle deletion deliberately — or use soft deletes.

Other keys you'll hear about

• Unique key / unique constraint: no duplicates allowed (like email above), but it's not the row's identity, and in most databases it allows NULLs.
• Composite key: a key made of several columns together. A course_enrollments table might use (student_id, course_id) as its primary key, so a student can't enroll in the same course twice.
• Natural vs surrogate key: a natural key is real-world data (a passport number); a surrogate is an invented ID. Use surrogates for primary keys and add unique constraints for natural identifiers.

Why AI-built schemas sometimes skip foreign keys

Some AI-generated code — and some ORMs and hosted databases — create tables with customer_id columns but no actual foreign key constraint. Everything looks fine until a bug deletes a customer and leaves orphaned orders, or creates an order for a user ID that doesn't exist.

Check your schema: each something_id column should have a REFERENCES constraint. Ask your AI tool to add missing ones in a migration — after cleaning up any orphaned rows that would violate them.

The summary

• A primary key uniquely identifies each row; every table has one.
• A foreign key links to another table's primary key, and the database enforces that the link is valid.
• Index foreign key columns yourself in Postgres.
• Choose ON DELETE behaviour deliberately; be wary of CASCADE on valuable data.

---

EasySpawn servers include PostgreSQL with daily backups, so Claude Code can add the missing constraints in a real migration, test it against real data, and you can restore if anything goes wrong. See how it works or join the waitlist.

Related: How to Design Your First Database · SQL Joins Explained · What Is CRUD? · Database Transactions Explained