Part 4 of 5 · 2 min read
Foreign key
How one table points at a row of another, and what that reference must point to.
Foreign key
Think of a coat-check ticket. The ticket number means nothing on its own; it only works because it matches a number already written on a coat hanging on a specific rack.
In plain words: a foreign key is a column, or set of columns, in one table that must match the value of a candidate key, usually the primary key, in another table. It is how one row points at exactly one row elsewhere, without copying that row's data. A foreign key does not have to be unique in its own table; many orders can point at the same customer.
A worked example. orders.customer_id is a foreign key into customers.customer_id. Many rows of orders can hold the same customer_id, since one customer can place many orders. Every value that does appear in orders.customer_id must match some existing row's customer_id in customers; a database enforcing this rule rejects an order for a customer that does not exist.
The key lines.
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
placed_on DATE NOT NULL
);
Foreign key, referential integrity
A foreign key is a column, or set of columns, required to match a candidate key's value in another table (or the same table). Referential integrity is the rule that every foreign key value must match an existing row in the table it references, enforced automatically once the foreign key is declared.
*Example:* `orders.customer_id REFERENCES customers(customer_id)` is a foreign key; referential integrity is what stops an insert into `orders` with a `customer_id` that no row of `customers` actually has.
For the curious A foreign key can reference its own table
A foreign key is not required to point at a different table. An employees table with a manager_id column referencing employees.employee_id models "who this employee reports to," using the same table on both sides of the reference.
Quick notes
- A super key is any unique set of columns; a candidate key is a super key with no wasted columns.
- A primary key is the one candidate key chosen as a table's official identifier.
- A composite key uses more than one column together, when no single column is unique alone.
- A foreign key references a candidate key (usually the primary key) in another table, and need not be unique itself.
- Referential integrity is the rule that every foreign key value must match a real row in the table it references.