A foreign key is a column or set of columns in one table whose values must match a primary key or unique key in another table, or in the same table. Among database fundamentals it is the mechanism that turns separate tables into a connected relational database: it records relationships such as an order belonging to a customer, and the database management system enforces them.
The table that holds the foreign key is called the child or referencing table; the table it points to is the parent or referenced table. The guarantee that every non-NULL foreign key value has a matching parent row is known as referential integrity, and the foreign key is the database constraint that delivers it.
How a foreign key constraint works
A foreign key is declared in the schema, usually in CREATE TABLE or ALTER TABLE, with a REFERENCES clause naming the parent table and column. From that moment the DBMS checks every write that could break the relationship and rejects it with a constraint violation error.
The check runs in both directions. Inserting or updating a child row requires that the referenced value exists in the parent. Deleting or updating a parent row requires a decision about the child rows that still point to it, which the referential action defines. The referenced column must carry a primary key or unique constraint, because a reference to a value that may appear twice would be ambiguous.
Foreign keys may span several columns, mirroring a composite key in the parent, and the column types must be compatible. A foreign key may also point to its own table, a self-referencing foreign key, which models hierarchies such as employees and their managers or categories and subcategories.
| Operation | Table affected | Check performed |
|---|---|---|
| Insert child row | Referencing table | Parent value must exist |
| Update child key | Referencing table | New value must exist in parent |
| Delete parent row | Referenced table | Apply the ON DELETE action |
| Update parent key | Referenced table | Apply the ON UPDATE action |
| Insert parent row | Referenced table | No foreign key check needed |
Referential actions on delete and update
The referential action tells the database what to do with child rows when the parent row is deleted or its key changes. The SQL standard defines five actions, and the choice expresses a business rule, not merely a technical preference.
| Action | Effect on child rows | Typical use |
|---|---|---|
| NO ACTION | Reject the change if children exist | Default in most systems |
| RESTRICT | Reject immediately, even if deferrable | Protecting financial records |
| CASCADE | Delete or update children as well | Order lines owned by an order |
| SET NULL | Set the foreign key to NULL | Optional link, such as assigned agent |
| SET DEFAULT | Set the foreign key to its default | Fallback category |
The difference between NO ACTION and RESTRICT is subtle: in systems such as PostgreSQL, NO ACTION can be deferred to the end of the transaction, while RESTRICT is checked at once. CASCADE is powerful and dangerous, because one delete can remove thousands of rows across several tables; it suits true ownership, never loose association.
Foreign keys compared with related keys
A foreign key is one of several key concepts in relational design, and each one answers a different question. The primary key identifies a row, while the foreign key refers to a row elsewhere.
| Concept | Question it answers | Uniqueness | NULL allowed |
|---|---|---|---|
| Primary key | Which row is this | Unique | No |
| Foreign key | Which row elsewhere does this refer to | Not required | Yes, unless NOT NULL |
| Unique key | Which alternative value is unique | Unique | Usually yes |
| Composite key | Which column combination identifies a row | Unique as a set | No, when primary |
Because a foreign key does not have to be unique, it naturally models one-to-many relationships. Adding a unique constraint on the foreign key column turns the relationship into one-to-one, and a junction table with two foreign keys models many-to-many, such as students and courses.
NULL in a foreign key means that no relationship exists, and the constraint is not checked for that row. For multi-column keys, the default MATCH SIMPLE rule skips the check if any column is NULL, while MATCH FULL requires all columns to be NULL or all to be filled.
Performance and indexing considerations
Foreign keys have a runtime cost, and the most common performance problem is a missing index on the child column. The parent side always has an index, because a primary or unique key requires one, but many systems do not create an index on the referencing column automatically.
- Without an index on the child column, every parent delete or key update forces a full scan of the child table, which can lock large tables in OLTP systems.
- MySQL with InnoDB creates the child index automatically, while PostgreSQL and SQL Server do not, so it must be added explicitly.
- Bulk loads are often faster with constraints temporarily disabled or deferred, but they must be validated afterwards, or orphan rows slip in.
- Each insert into the child performs an index lookup in the parent, a small cost that is negligible for most workloads.
The same child-column index also speeds up the JOIN queries that follow the relationship, so it typically pays for itself twice.
When teams omit foreign keys and what it costs
Some architectures deliberately skip declared foreign keys, for example in sharded databases, data warehouses or services that own separate databases. The relationship still exists in the data, but integrity moves from the database into application code or batch checks.
| Aspect | Declared foreign key | Application-enforced |
|---|---|---|
| Integrity guarantee | Enforced on every write | Depends on every code path |
| Orphan rows | Impossible | Likely over time |
| Cross-database links | Not supported | Possible |
| Write overhead | Small lookup per write | None in the database |
| Documentation value | Visible in the schema | Hidden in code |
The decisive recommendation for transactional systems is to declare foreign keys by default and remove them only for a measured reason. Orphan rows created by a missed code path are expensive to find and repair, and they silently corrupt reports.
A plain-language summary
A foreign key is a reference that the database refuses to let break. Imagine a library where every loan slip must name a member who really exists in the member register. A foreign key is the rule that rejects a slip with an unknown member number.
It also decides what happens when a member leaves: either the library refuses to delete the member while loans are open, or it removes the loans too, or it simply leaves the name blank. The database applies this rule automatically, every single time.
An example from practice
A small accounting application stores invoices in one table and invoice lines in another. The lines table has a column invoice_id declared as a foreign key referencing invoices(id) with ON DELETE CASCADE, because a line has no meaning without its invoice. A separate foreign key from invoices to customers uses ON DELETE RESTRICT, because customers with invoices must never disappear.
One afternoon a support script tries to delete a duplicate customer record that still has 14 invoices. The database rejects the delete with a constraint violation, and the operator merges the invoices into the correct customer first. Later, the team notices that deleting old invoices is slow; adding an index on invoice_lines.invoice_id reduces the cleanup job from several minutes to a few seconds, because the cascade no longer scans the whole lines table.