Foreign key

What is a foreign key?

A foreign key is a column, or set of columns, whose values must match a row that already exists in another table. An orders table might hold a customer_id that points at the customers table, and the constraint tells the database to reject any order whose customer_id has no matching customer. This is what relational databases call referential integrity: a reference in one table always resolves to a real row in the other.

Where a primary key answers which row this is, a foreign key answers which row over there it points to. The target has to be guaranteed unique, so a foreign key points at the other table's primary key or at another candidate key backed by a unique constraint. If that target is a composite key, the foreign key must list the same columns in the same order.

Without the constraint, nothing stops an application from saving an order for customer 999 when no such customer exists. That row becomes an orphan. A foreign key turns that silent data problem into an error at write time.

Referential actions on delete and update

The harder question is what should happen when the referenced row is deleted, or its key is updated. SQL lets you choose the behaviour per foreign key, through an ON DELETE and ON UPDATE clause.

CREATE TABLE orders (
  order_id    integer PRIMARY KEY,
  customer_id integer NOT NULL,
  FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id)
    ON DELETE RESTRICT
);

The actions you can choose from:

• CASCADE. Deleting the customer also deletes their orders, and updating the customer's key copies the new value into the orders. Convenient, and an easy way to lose more data than you meant to.

• SET NULL. The order stays, but its customer_id becomes null. The referencing column has to allow nulls for this to work.

• SET DEFAULT. The referencing column falls back to its declared default value instead of null.

• RESTRICT and NO ACTION. Both block the delete while orders still reference the customer. NO ACTION is what you get when you write no clause at all.

The engines differ in the detail. In PostgreSQL, NO ACTION lets the check wait until the end of the transaction, so another statement can fix the reference first, while RESTRICT refuses the delete immediately and cannot be deferred. In MySQL's InnoDB engine the two are identical, both reject the change at once, and SET DEFAULT is parsed but then rejected outright. Microsoft SQL Server has no RESTRICT keyword: you get NO ACTION, CASCADE, SET NULL and SET DEFAULT, with NO ACTION as the default. For accounting or audit data, automatic cascade deletes are usually the wrong choice.

Index the referencing column

Declaring a foreign key and indexing it are two separate things, and the engines disagree on whether you get the index for free. The referenced side is always indexed, because it has to be a primary key or unique constraint. The referencing side is the surprise. MySQL's InnoDB requires an index on the referencing columns and creates one automatically if you did not. PostgreSQL and SQL Server do neither, and let you declare the foreign key with no index on the child table at all.

That matters because every delete or key change on the parent scans the child for matching rows, and every join between the two tables reads the same column. On a large child table an unindexed foreign key turns those into full scans, so on PostgreSQL and SQL Server, adding that index yourself is the normal thing to do.

Foreign key versus join

These two are easy to mix up, because they touch the same columns. A join is a read: a query that matches rows from two tables and returns them together. A foreign key is a rule the database maintains on every write so the relationship stays valid. You can join orders.customer_id to customers.customer_id with no foreign key at all, and the join still runs. What you lose is the guarantee: nothing has checked that every customer_id in orders exists in customers, so the join can silently drop the orders whose customer went missing. The foreign key is what lets you trust that the join is complete.

Foreign keys in analytical warehouses

Operational databases enforce foreign keys because they take many small writes and need each one to be correct. An analytical data warehouse often declares the same relationships but does not enforce them. Amazon Redshift calls primary key, unique and foreign key constraints informational only and does not enforce them on load. Snowflake enforces none of them on its standard tables either, only NOT NULL.

They are still worth declaring. Redshift's query planner reads the primary and foreign keys as hints and uses them to reorder joins and drop redundant ones. The catch is that it trusts them: if a foreign key is declared but the data breaks it, a query can return wrong results, such as duplicate rows from a SELECT DISTINCT that assumed a unique key. So the integrity check does not disappear, it moves to a data testing step or a data contract that confirms every fact row points at a real dimension before the load is published.

The shape of the key matters here too. A star schema fact table usually stores the surrogate key of each dimension row rather than its business key, so the reference stays stable when the source reissues a code, and it keeps pointing at the right version of a slowly changing dimension.