What Is a Foreign Key? The Rule That Stops Orphan Rows
A foreign key makes the database refuse rows that point at nothing. Here is the SQL, what each ON DELETE option does, and the missing index that bites later.
A foreign key is a rule on a column that says every value in it must match a row in another table. If an order points at product 42, the database checks that product 42 exists. If it does not, the insert is rejected. That check is called referential integrity, and it is what keeps your data from filling up with rows that point at nothing.
Two tables are involved. The one holding the foreign key is the referencing table. The one it points at is the referenced table. In PostgreSQL's own example, orders refers to products:
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);With that in place, as the PostgreSQL constraints documentation puts it, it is impossible to create an order with a non-null product_no that does not appear in the products table.
Foreign key versus primary key
A primary key identifies a row inside its own table. A foreign key is a pointer to another table's key. One table usually has one primary key and can have many foreign keys.
The target of a foreign key has to be a primary key or a unique column, so the lookup is always fast and unambiguous. If you leave out the column list (REFERENCES products), PostgreSQL assumes the primary key.
What happens when the parent row is deleted
This is where foreign keys earn their keep, and where AI-written schemas make quiet, expensive choices. The ON DELETE clause decides what happens to child rows when the parent disappears.
NO ACTION (the default) lets the delete attempt proceed, then raises an error if a child row would be left pointing at nothing.
RESTRICT blocks the delete outright, and unlike NO ACTION it cannot be postponed to the end of a transaction.
CASCADE deletes the child rows along with the parent.
SET NULL keeps the child rows and blanks the pointer.
SET DEFAULT keeps the child rows and sets the pointer to the column default, which fails if the default does not itself point at a real row.
The PostgreSQL docs show the sensible pairing. Order lines belong to an order, so ON DELETE CASCADE on the order: delete the order and its lines go too. But a product is an independent thing, so ON DELETE RESTRICT on the product: you cannot delete a product that still appears in an order line.
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);The two mistakes to look for in generated schemas
Cascade everywhere. An assistant asked for a "clean" schema will sometimes put ON DELETE CASCADE on every foreign key because it makes errors go away. Then deleting one customer silently deletes their invoices, their payments and their audit trail. Cascade is correct for data that has no meaning without its parent. For records you must keep, use RESTRICT, or soft delete instead of deleting at all.
No index on the referencing column. PostgreSQL does not create an index on the foreign key column for you. The docs point out that deleting or updating a parent row means scanning the child table for matches, so the referencing columns are often worth indexing yourself. Without it, deleting one parent row can turn into a full table scan. This is a classic cause of an app that is fast with 100 rows and crawls with 100,000. Read what a database index is if that term is new.
Foreign keys and NULL
A foreign key column can be left empty (NULL) unless you also mark it NOT NULL, and an empty value skips the check. That is useful for optional links, such as a task with no assignee yet. It is a bug when the link is required, so declare required relationships NOT NULL and let the database refuse the rest.
How to ask your coding agent for them
Review the schema. For every column that stores another table's id, confirm
it has a foreign key constraint. For each one, state the ON DELETE behavior
you chose and why: CASCADE only where the child has no meaning without the
parent, RESTRICT otherwise. Add an index on every referencing column. Mark
required relationships NOT NULL. Do not change data, only the schema, as a
new migration.For more on getting a good schema out of an assistant, see how to prompt AI to design a database schema. When a delete goes wrong anyway, what happens if an AI agent deletes your database covers the recovery side. The wider picture lives in the guide to building an app with AI.
FAQ
What is a foreign key in simple terms?
It is a column whose values must match an existing row in another table. It stops you saving an order for a product that does not exist.
Is a foreign key the same as a primary key?
No. A primary key uniquely identifies a row in its own table. A foreign key points at another table's key. A column can be both, for example in a one-to-one relationship.
What does ON DELETE CASCADE do?
When you delete the parent row, the database also deletes every child row that points at it. Use it only when the child data is meaningless without the parent.
Do foreign keys slow down my database?
They add a small check on writes, and deletes on the parent table need to find matching child rows, which is fast only if the referencing column is indexed. In exchange you get data that cannot quietly go wrong.
Can a foreign key be null?
Yes, unless the column is also declared NOT NULL. A null value skips the check, which suits optional relationships and is a bug for required ones.
How did this land?
About the author

Senior Editor, AI & Product
Cecilia leads the Swarmz editorial desk. She has spent a decade turning complex AI and product topics into writing people actually finish, and she owns the blog's quality bar.


