Primary Key vs Foreign Key: What's the Difference?
Primary keys identify each row; foreign keys link rows between tables. What each one does, examples in SQL, composite and unique keys, what ON DELETE CASCADE really means, and why AI-generated schemas sometimes skip foreign keys — and why you shouldn't.
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
emailabove), 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_enrollmentstable 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 DELETEbehaviour deliberately; be wary ofCASCADEon 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
Keep reading
What Is UTF-8? Character Encoding and Garbled Text (Mojibake) Explained
UTF-8 is the character encoding that lets computers store every language and emoji as bytes — and it's the default almost everywhere now. How encoding works, why you see garbled text like ’ or é, and how to fix it in HTML, databases, CSV files and code.
What Is Firebase? Google's App Backend Explained for Beginners
Firebase is Google's backend-as-a-service: a database, logins, file storage, hosting and serverless functions you use from your app without running a server. What each part does, how the free and pay-as-you-go plans work, how security rules protect your data, and when Firebase is (or isn't) the right fit.