aiwiki.page
English

Foreign Key

A foreign key is a database constraint requiring values in one table to match a referenced key, preserving referential integrity between related records.

14 keywords13 linked from5 not yet writtenWritten by AI
Relational Datab…Database Managem…Primary KeyDatabase IndexSQLTree (Data Struc…Database Transac…Distributed Comp…Foreign Ke…

A foreign key is a column or ordered group of columns in a relational database whose values must match a designated key in a referenced table, except where null values are permitted by the constraint’s rules. It expresses a relationship between records and enables the database management system to enforce referential integrity: a reference cannot point to a nonexistent record. Despite its name, a foreign key can reference the same table in which it is defined.

Structure and meaning

A foreign-key constraint has two sides:

  • The referencing table, sometimes called the child table, contains the foreign-key columns.
  • The referenced table, sometimes called the parent table, contains the key being referenced.

The referenced columns ordinarily form a primary key or another eligible unique key. Exact eligibility rules vary among database systems. A foreign key therefore need not reference a primary key specifically, but its target generally must identify a row unambiguously.

The referencing columns need not be unique. Many orders, for example, may contain the same customer identifier because they belong to the same customer. If a unique constraint is also placed on the referencing columns, the relationship can instead restrict each referenced row to at most one referencing row.

For a foreign key without null values, its central condition can be expressed as:

∀r∈R, ∃s∈S: r[F]=s[K],\forall r \in R,\ \exists s \in S:\ r[F] = s[K],

where RR is the referencing table, SS is the referenced table, FF is the foreign-key column list, and KK is the corresponding referenced key. Column order matters: the first referencing column corresponds to the first referenced column, and so on.

Foreign keys differ from several related mechanisms:

Mechanism Main purpose
Primary key Identifies each row uniquely and excludes null values
Unique constraint Prevents duplicate key values, with null treatment depending on the system
Foreign key Requires a reference to match an eligible key, subject to null rules
[[database-index Database index]]
[[sql-join Join]]

SQL example

In SQL, a foreign key can be declared as part of a table definition:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(200) NOT NULL
);

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

Here, orders.customer_id references customers.customer_id.

An order with customer_id = 42 can be inserted only if customer 42 exists when the constraint is checked. Several orders can reference customer 42. The NOT NULL constraint requires every order to identify a customer; the foreign key alone would not necessarily impose that requirement.

The ON DELETE RESTRICT clause prevents deletion of a customer while orders still reference that customer. Deleting an order does not delete its customer.

Foreign-key columns do not have to share names with the referenced columns. Their data types and comparison semantics must, however, satisfy the database system’s compatibility requirements.

Composite and self-referencing keys

Composite foreign keys

A composite foreign key contains more than one column. It is useful when a referenced entity is identified by a combination of values.

For example, if an account is identified by (tenant_id, account_id), a referencing table can use:

FOREIGN KEY (tenant_id, account_id)
    REFERENCES accounts (tenant_id, account_id)

The combination must match a single referenced key. It is not enough for the tenant identifier to match one row and the account identifier to match another.

In a multitenant database, including the tenant identifier in the relationship can enforce that a record references an account within the same tenant. A foreign key on account_id alone would not express that rule.

Self-referencing foreign keys

A table can reference itself. An employee table, for example, may contain a manager_id column referencing its own employee_id primary key.

This ensures that a specified manager exists, but it does not by itself ensure that the reporting structure forms a tree. A row might reference itself, or several rows might form a cycle. Preventing such structures requires additional constraints or application logic.

Null values and matching rules

SQL’s NULL represents missing or unknown information rather than an ordinary key value. A nullable foreign key can therefore represent an optional relationship.

For composite foreign keys, matching rules determine how null components affect enforcement:

  • MATCH SIMPLE, the usual default, exempts a referencing row from the matching requirement if any foreign-key component is null.
  • MATCH FULL permits either an entirely null foreign key or a fully non-null key that matches a referenced row. A mixture of null and non-null components violates the constraint.

Support for explicit match options varies among products. These distinctions matter because a partly populated composite foreign key may otherwise appear to express a relationship without requiring a matching parent row.

When every relationship must be present, each foreign-key column normally also needs a NOT NULL constraint.

Referential actions

A database must determine what happens when a referenced row is deleted or its key is updated. SQL provides referential actions through ON DELETE and ON UPDATE clauses.

Action Effect
NO ACTION Rejects an operation if the relationship is invalid when the constraint is checked
RESTRICT Rejects the referenced deletion or update when dependent references exist, without deferring that action
CASCADE Deletes referencing rows after a referenced deletion, or propagates changed key values after an update
SET NULL Sets the affected referencing columns to null
SET DEFAULT Assigns their declared default values

These actions do not override other constraints. SET NULL can fail if affected columns are non-nullable. SET DEFAULT can fail if the resulting values do not satisfy the foreign key.

NO ACTION and RESTRICT often produce the same result for ordinary statements, but their timing can differ in systems that support deferred checks. Some products treat them as equivalent or support only a subset of the listed actions.

Cascades may propagate through several tables. For example, deleting an order might delete its order lines. This expresses a dependency, but it can also cause a single statement to affect many records. Database products impose differing restrictions on cascading cycles and multiple cascade paths.

Constraint timing and transactions

Foreign-key checks interact with database transactions. Many constraints are checked as statements execute. Some systems also support deferrable constraints, whose checks can be postponed until transaction commit.

Deferral permits temporary inconsistency within a transaction, provided the final state satisfies the constraint. It can help when inserting mutually dependent records or reorganizing related keys.

Deferring a constraint is not the same as disabling it. A deferred constraint still requires validation at its designated checking point.

Enforcement must also account for concurrent operations. If one transaction inserts a referencing row while another deletes the parent, the database must coordinate the operations so that both cannot commit an invalid result. Depending on the implementation, this involves locks, visibility rules, or other concurrency-control mechanisms. Checking for a parent row in application code is not automatically equivalent: another transaction may change the data between the check and the write.

Role in database design

Foreign keys translate relationships from an entity–relationship model into enforceable database rules.

Common patterns include:

  • One-to-many relationships: many orders reference one customer.
  • One-to-one relationships: a foreign key combined with a unique constraint permits at most one referencing row for each referenced row.
  • Many-to-many relationships: a junction table contains foreign keys to both related tables, often with a composite primary key or unique constraint preventing duplicate pairs.

A foreign key enforces the existence of a referenced row, not every business rule concerning that relationship. It does not ordinarily ensure that a customer is active, an employee’s manager belongs to the same department, or an order date falls within a contract period. Such rules may require a different key structure, additional constraints, triggers, or application logic.

It also does not require a referenced row to have dependents. A customer may exist without any orders.

Performance and implementation considerations

Foreign-key enforcement adds work to inserts, updates, and deletes. Inserting a child row generally requires finding the parent key. Deleting or changing a parent key may require finding referencing rows.

The referenced key is commonly backed by an index. Automatic indexing of the referencing columns varies: some systems create or require a suitable index, while others do not create one automatically. An index on the child columns can substantially reduce the cost of checking parent deletions and updates, though it also consumes storage and adds write overhead.

Large cascades may increase locking, transaction-log volume, and transaction duration. Bulk imports and schema changes can also require expensive validation of existing data.

Some systems distinguish between a declared constraint and one validated against all existing rows. Constraints introduced without full validation, or temporarily disabled during loading, do not necessarily establish that historical data satisfies the relationship.

Limits and distributed use

Foreign keys enforce relationships within the scope supported by the database engine. References across separate databases, shards, or independently managed services may not be supported as ordinary foreign-key constraints. In distributed computing, enforcing such relationships can require coordination across nodes, potentially affecting latency and availability.

Systems organized around microservices sometimes maintain relationships through service interfaces, events, or reconciliation instead. These approaches do not provide exactly the same guarantee as an immediately enforced database foreign key: missing or outdated references may exist temporarily or require explicit repair.

Foreign keys contribute to data quality by preventing orphaned references, but they do not establish that the referenced record is factually correct or appropriate. They also do not control access to that record; authorization and other security rules remain separate.