FOREIGN KEY Constraint in SQL

Introduction

The SQL FOREIGN KEY constraint creates and enforces a relationship between rows in related tables. It prevents a child row from referring to a parent key that does not exist, subject to the relationship's NULLability and update or delete rules. This protection is called referential integrity.

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    CONSTRAINT fk_orders_customer
      FOREIGN KEY (customer_id)
      REFERENCES customers(customer_id)
);

Here, customers is the parent and orders is the child. Every non-NULL orders.customer_id must match an allowed key in customers. The constraint turns an intended association into a database rule that protects every writer.

Parent Table, Child Table, and Referenced Key

The parent table owns the referenced candidate key. The child table stores a foreign-key value that points to it. The referenced column is commonly the parent's primary key, although many database systems also allow a compatible UNIQUE candidate key.

Direction matters. A customer does not store every order ID; each order stores the customer it belongs to. The parent can exist before any child exists, while a mandatory child cannot reference a missing parent.

Basic and Named Syntax

A foreign key can be declared when the child table is created. Giving the constraint a meaningful name makes errors, schema inspection, and future migrations easier to understand.

CREATE TABLE invoices (
    invoice_id BIGINT PRIMARY KEY,
    account_id BIGINT NOT NULL,
    CONSTRAINT fk_invoices_account
      FOREIGN KEY (account_id)
      REFERENCES accounts(account_id)
);

A useful naming convention is fk_<child>_<role>. Role is especially important when the same table is referenced more than once, such as created_by_user_id and approved_by_user_id.

What Referential Integrity Enforces

For an insert or update in the child, each non-NULL foreign-key value must match one referenced parent key. For a parent delete or key update, the database applies the declared referential action. Invalid operations fail atomically instead of creating a broken reference.

A foreign key enforces existence, not every business condition. It can prove that customer 101 exists, but it does not automatically prove that the customer is active, permitted to order, or valid for a particular region.

Foreign Keys Prevent Orphan References

An orphan is a child row whose supposed parent cannot be found. Orphans make reports incomplete, joins misleading, and updates ambiguous. They often appear after bad imports, incorrect deletion order, disabled constraints, or application-only validation.

INSERT INTO orders (order_id, customer_id)
VALUES (5001, 9999);

-- Rejected when customer 9999 does not exist.

The rejected statement is useful evidence of an invalid workflow. Removing the constraint to make the error disappear transfers the defect into stored data and makes later diagnosis harder.

FOREIGN KEY Is Not the Same as JOIN

A JOIN is a query operation that combines rows at read time. A foreign key is a schema rule that controls which values may be stored. Queries can join columns without constraints, and a foreign key does not automatically join tables for a query.

Use joins to retrieve related data and constraints to protect the relationship. They solve different problems and normally belong together in a relational design.

PRIMARY KEY, UNIQUE, and FOREIGN KEY

A primary key identifies one row in its own table. A UNIQUE constraint protects an alternate candidate key. A foreign key stores a reference to a permitted parent key. The same column can participate in more than one rule when the model requires it.

Foreign-key values normally repeat. Many orders can reference one customer, so repeating customer_id = 101 is correct. Adding UNIQUE to the child key changes the relationship from one-to-many to at most one-to-one.

One-to-Many Relationships

One-to-many is the common foreign-key pattern. One department can contain many employees, one order can contain many items, and one customer can place many orders. The foreign key belongs on the many side.

CREATE TABLE order_items (
    order_item_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL,
    CONSTRAINT fk_items_order
      FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

The child key is not UNIQUE because several items belong to the same order. A supporting index may still be valuable for lookup and parent-change checks.

One-to-One Relationships

A foreign key alone does not guarantee one-to-one. To allow at most one profile per user, make the child foreign key UNIQUE, or use it as the child's primary key.

CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY,
    biography VARCHAR(1000),
    CONSTRAINT fk_profile_user
      FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Whether every user must have a profile is a separate participation rule. The foreign key protects each existing profile, but additional workflow or deferred validation may be needed to require a child for every parent.

Many-to-Many Relationships

Many-to-many relationships are modeled with a junction table containing two foreign keys. Students can take many courses, and each course can have many students. The junction row represents one enrollment.

CREATE TABLE enrollments (
    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    enrolled_at TIMESTAMP NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

The composite primary key prevents duplicate enrollment pairs. A surrogate enrollment ID can also be used, but the business pair should remain protected by a UNIQUE constraint if duplicate enrollment is forbidden.

Nullable and Mandatory Relationships

A nullable foreign key represents an optional relationship. SQL foreign-key checking normally permits NULL because NULL means no parent has been assigned. A matching parent is required only when the value is non-NULL.

Combine NOT NULL with FOREIGN KEY for a mandatory relationship: the child must name a valid parent. Choose NULLability from business meaning, not convenience. Fake values such as zero or an arbitrary "unknown" parent often hide missing data and distort reporting.

Referencing a UNIQUE Candidate Key

A foreign key can commonly reference a parent key protected by UNIQUE rather than the primary key. This is useful when the relationship intentionally uses a stable alternate identifier such as a governed country code.

CREATE TABLE countries (
    country_id BIGINT PRIMARY KEY,
    country_code CHAR(2) NOT NULL UNIQUE
);

CREATE TABLE addresses (
    address_id BIGINT PRIMARY KEY,
    country_code CHAR(2) NOT NULL,
    FOREIGN KEY (country_code)
      REFERENCES countries(country_code)
);

The referenced values must be unique so one child value identifies at most one parent row. Vendor requirements for candidate indexes and NULL handling should be checked.

Data Type Compatibility

Parent and child columns should use compatible data types, sizes, signedness, character sets, and collations. A BIGINT parent paired with a narrow integer child can overflow. Different string collations may compare the same text differently or prevent the constraint from being created.

Reuse domain definitions or migration helpers so identifiers remain consistent. Type alignment also improves joins, indexing, query plans, and application bindings.

Composite Foreign Keys

A composite foreign key references a composite candidate key as one relationship. This is common for tenant-scoped or location-scoped identities.

CREATE TABLE warehouses (
    company_id BIGINT NOT NULL,
    warehouse_code VARCHAR(20) NOT NULL,
    PRIMARY KEY (company_id, warehouse_code)
);

CREATE TABLE inventory (
    inventory_id BIGINT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    warehouse_code VARCHAR(20) NOT NULL,
    FOREIGN KEY (company_id, warehouse_code)
      REFERENCES warehouses(company_id, warehouse_code)
);

Column count, order, and compatible types must correspond. The pair must match one parent row; matching each component to unrelated rows is not sufficient.

NULLs in Composite Foreign Keys

Composite NULL behavior can surprise teams. Under common matching rules, a partly NULL child key may escape normal matching because the complete relationship is unknown. Vendor options and SQL match semantics differ.

If the relationship is all-or-nothing, use NOT NULL on every component or add a CHECK constraint requiring either all components to be NULL or all to be present. Document the intended rule and test it on the production database engine.

Self-Referencing Foreign Keys

A table can reference itself. An employee's optional manager is another employee, and a category's parent is another category.

CREATE TABLE employees (
    employee_id BIGINT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    manager_id BIGINT NULL,
    CONSTRAINT fk_employee_manager
      FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

The constraint prevents references to nonexistent employees, but it does not prevent every logical cycle. Additional checks are needed to forbid self-management or longer loops such as A reporting to B while B reports to A.

Multiple Foreign Keys to the Same Table

One child can reference the same parent table in different roles. A document may have created_by_user_id, reviewed_by_user_id, and approved_by_user_id, each pointing to users.user_id. These are three separate relationships with potentially different NULLability and lifecycle rules.

Name columns and constraints by role rather than using vague names such as user_id_1. Decide independently whether each role is mandatory, whether the user may be deactivated, and what historical identity must remain when staff records change.

Cyclic Relationships and Deferred Checking

Two tables can depend on each other, or a longer chain can return to its starting table. Cycles complicate insert order, deletion, fixture creation, and migrations. Often the model can be simplified by making one relationship optional during creation or by introducing a separate association table.

Some database systems support deferrable constraints that are checked at transaction commit instead of after each statement. This can permit a valid final state when intermediate statements temporarily violate ordering. Support and syntax vary, so do not design around deferred behavior without verifying the production DBMS and transaction mode.

Insert Order and Transactions

With immediate checking, insert the parent before the child. When deleting under restrictive behavior, delete or reassign children before deleting the parent. A transaction keeps the complete business operation atomic.

The foreign key does not ensure that all expected child rows were created. It protects each stored reference, while transaction logic and business validation protect completeness, totals, inventory movement, and other multi-row rules.

Referential Actions

Referential actions define what happens to children when a referenced parent key is deleted or updated. Common actions are RESTRICT, NO ACTION, CASCADE, SET NULL, and, where supported, SET DEFAULT.

The correct action follows lifecycle ownership. Ask whether the child should block removal, disappear with the parent, remain without that relationship, or move to a meaningful default. Do not select an action simply because it avoids an error.

RESTRICT and NO ACTION

Restrictive behavior rejects a parent delete or key update while dependent children exist. This is often a safe default for customers, accounts, departments, products, and other independently meaningful records.

RESTRICT and NO ACTION can differ in timing, especially where deferrable constraints are supported. Some products treat them similarly. Confirm the target DBMS rather than assuming universal equivalence.

ON DELETE CASCADE

CASCADE automatically deletes dependent child rows when the parent is physically deleted. It fits tightly owned components that have no meaning without the parent, such as order items when an uncommitted draft order is removed.

FOREIGN KEY (order_id)
  REFERENCES orders(order_id)
  ON DELETE CASCADE

Cascades can travel through several tables, generating large deletes, locks, logs, replication lag, and difficult recovery. Review the full dependency graph and test realistic row counts before enabling them.

ON DELETE SET NULL

SET NULL keeps the child but removes the relationship when the parent is deleted. The foreign-key columns must permit NULL. This can fit an employee whose former manager record disappears while the employee remains meaningful.

It is inappropriate when every child must retain a parent or when clearing the link destroys historical meaning. The resulting NULL must have a clear business interpretation.

ON DELETE SET DEFAULT

SET DEFAULT assigns the child column's default after parent deletion where the DBMS supports it. The default normally must itself satisfy the foreign key. A default such as an explicit, governed "unassigned" category can be valid, but a fabricated parent used only to silence errors is usually misleading.

Prefer NULL when the relationship is genuinely unknown and optional. Use a default parent only when it represents a real domain state with documented reporting and lifecycle rules.

ON UPDATE CASCADE

ON UPDATE CASCADE propagates a changed referenced key to children. Stable surrogate primary keys rarely change, reducing the need for this behavior. Mutable natural keys may require it, but a key update can still touch many rows and indexes.

Ordinary business attribute changes, such as a customer's name or an item's price, should not change a stable identifier. Separating identity from mutable descriptions simplifies relationships.

Soft Deletes and Historical Records

A soft delete changes a flag or timestamp; it is an UPDATE, not a physical DELETE. Therefore, ON DELETE actions do not run. The parent still exists from the foreign key's perspective.

If children may reference only active parents, enforce that workflow through application logic, procedures, triggers, or a carefully designed model. Historical orders may intentionally keep a reference to an inactive customer while also storing snapshot values such as shipping name and address. Referential integrity and historical truth are related but different concerns.

Parent Existence Is Not Business Eligibility

A foreign key answers whether the referenced key exists, not whether the row is currently eligible for a particular operation. An existing product may be discontinued, an account may be frozen, a course may be closed, or an employee may lack approval authority.

These rules often involve status, time, tenant, amount, or several tables and belong in transactional application or database logic. Use CHECK constraints for rules local to one row and preserve the foreign key for existence. Combining constraints gives clearer responsibility than trying to force one mechanism to express every rule.

Historical Snapshots Can Be Correct Duplication

Historical records often need both a foreign key and copied descriptive values. An order can reference the current customer while preserving the name and delivery address used when the order was placed. An invoice line can reference a product while retaining the sold description and price.

This is not careless denormalization when the copied values represent a time-specific business fact. The foreign key supports identity and navigation; snapshot columns preserve what was true for that transaction. Do not overwrite legally or operationally important history when the parent changes.

Application Validation Is Not Enough

An application can check that a parent exists and still lose a race if another transaction deletes it before the child insert. The database constraint resolves the final state under concurrency.

Applications should validate early to provide useful messages, while the foreign key remains the authoritative guard. APIs, imports, administration tools, scripts, background workers, and future services may all write the same tables.

Foreign Keys and Indexing

The referenced parent key already needs uniqueness and is normally indexed. Many DBMSs do not automatically create an index on the child foreign-key columns. Such an index often improves child lookup, joins, and parent delete or update checks.

CREATE INDEX ix_orders_customer_id
ON orders(customer_id);

Do not index every foreign key blindly. Consider selectivity, table size, query patterns, write overhead, existing composite indexes, and column order. A composite index beginning with the foreign-key columns may already provide the needed access path.

Performance and Concurrency

Foreign-key checks require work, but integrity has operational value. Missing indexes can make parent changes scan large child tables, hold locks longer, and increase deadlock or blocking risk. Cascades can amplify this effect.

Measure representative inserts, deletes, updates, and joins with production-like volume. Optimize schema and indexes before removing an essential relationship rule. The cost of hidden orphans, cleanup, and incorrect reports is also performance work, just delayed.

Finding Existing Orphans

Before adding a foreign key to an existing table, profile the data. A LEFT JOIN can identify child rows without parents.

SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c
  ON c.customer_id = o.customer_id
WHERE o.customer_id IS NOT NULL
  AND c.customer_id IS NULL;

NOT EXISTS is another clear option. Investigate each category of orphan. It may represent a bad source mapping, a missing legitimate parent, a historical exception, or an incorrect child value.

Repairing Orphan Data Safely

Do not delete orphans automatically. Correct the child reference, create a legitimate missing parent, map source identifiers, archive invalid history, reject bad source records, or redesign the relationship according to business meaning.

Replacing every orphan with an arbitrary "unknown" row can collapse distinct problems into one misleading category. NULL is usually more honest when the relationship is optional and genuinely unknown.

Adding a Foreign Key to an Existing Table

A short DDL statement can trigger a large operational task. The database may scan child data, probe parent indexes, acquire locks, write logs, consume I/O, and delay replication.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);

Assess DBMS-specific online, unvalidated, or deferred-validation features. A constraint that is enabled for new writes but not yet validated against history can support a staged migration, but its trust state must be understood and completed.

Safe Production Migration

  1. Define the relationship, cardinality, NULLability, and referential actions.
  2. Verify parent uniqueness and parent-child type compatibility.
  3. Find, classify, and repair orphan rows.
  4. Prevent new orphans while cleanup is running.
  5. Add supporting child indexes when measurements justify them.
  6. Estimate locks, logs, storage, runtime, and replication impact.
  7. Deploy with the safest DBMS-specific validation method.
  8. Validate all historical rows and confirm the constraint is trusted.
  9. Monitor violations, latency, blocking, and downstream consumers.

Validation and Trust States Differ by Database

Production databases provide different ways to separate enforcement of new writes from validation of old data. PostgreSQL supports patterns involving NOT VALID and later validation. SQL Server can distinguish enabled constraints from constraints trusted for optimization. Oracle provides several enable and validation states.

These features are useful for staged deployment, but they are not interchangeable. Record the intended final state, verify it through system metadata, and make completion observable. A constraint that exists in DDL but remains unvalidated or untrusted is unfinished migration work.

Bulk Loading and ETL

Bulk pipelines should normally load or reconcile parents before children. Staging tables can accept untrusted input, profile IDs, map source keys, and reject bad rows before data reaches constrained core tables.

Disabling foreign keys may speed a specialized load but creates a period in which invalid data can enter. If constraints must be disabled, use controlled authorization, guaranteed revalidation, explicit failure handling, and monitoring. Never leave a constraint disabled or untrusted after the load.

Late-Arriving Parent Data

Events and warehouse feeds sometimes deliver a child before its parent. The architecture must choose a policy: buffer and retry, load the parent first, stage the child, use a governed unknown dimension member, or reconcile asynchronously.

The choice depends on whether temporary inconsistency is acceptable and how history is interpreted. Bypassing validation without a reconciliation contract allows temporary disorder to become permanent corruption.

Foreign Keys in Data Warehouses

Warehouse fact tables commonly reference dimensions through surrogate keys, but enforcement choices depend on the platform, loading architecture, and scale. An explicit unknown dimension member may be meaningful for late-arriving data when its semantics and later reconciliation are governed.

Even when the warehouse engine does not enforce traditional foreign keys, the relationship still needs quality tests, rejected-row handling, lineage, and reconciliation metrics. Declaring unenforced metadata is not a substitute for proving that facts map to valid dimensions.

Foreign Keys in Microservices

A database foreign key works inside one database authority. Creating a direct constraint from one service's private database to another service's tables couples deployment, availability, ownership, and migrations.

Cross-service integrity is normally handled with APIs, events, outbox and inbox patterns, sagas, local snapshots, reconciliation, and compensation. Keep foreign keys within each service's local schema where they protect local invariants. Microservices are not a reason to abandon all relational constraints.

Partitioning, Sharding, and Replication

Partitioned and sharded databases place additional limits on foreign keys. Some systems require partition columns in referenced keys; cross-shard relationships may not be enforceable locally. Co-locating related rows can make integrity and joins practical.

Replication and CDC consumers must preserve ordering or tolerate temporary parent-child gaps. Schema changes, cascades, and bulk validation can increase logs and lag. Test failover, replay, and downstream handling rather than considering only the primary database.

Foreign Keys and ORMs

ORM relationship mappings help object navigation and unit-of-work behavior, but they do not replace database integrity. Data can enter outside the ORM, and configuration mistakes can still produce invalid writes.

Distinguish ORM cascades from database cascades. Enabling both without a clear owner can cause duplicate work, unexpected ordering, or confusing diagnostics. Define which layer creates, deletes, and reassigns each relationship.

API Error Handling

An API should translate a foreign-key violation into a meaningful domain response without exposing internal constraint details. An invalid parent supplied by the client may be a validation error, while a concurrent parent deletion may produce a conflict requiring retry or user action.

Log the constraint name and correlation context internally. Stable, descriptive constraint names make production diagnosis and error mapping much easier.

Ownership, Auditing, and Delete Policy

Delete behavior should follow data ownership and retention rules. A child that is merely associated with a parent may need RESTRICT, while a private component wholly owned by that parent may use CASCADE. Financial, compliance, and audit records often must remain even when the operational entity becomes inactive.

Document who may delete a parent, which descendants are affected, how many rows can change, and what evidence must survive. Prefer explicit archival or status transitions when physical deletion would erase business history. Database actions should reinforce the lifecycle policy rather than define it accidentally.

Monitor Relationships After Deployment

Track foreign-key violations by constraint, endpoint, job, and release. A sudden increase may reveal deployment order problems, stale caches, missing parent events, bad mappings, or a writer bypassing the expected transaction. Monitor parent-delete latency, lock waits, deadlocks, cascade row counts, and replication lag.

ETL and eventual-consistency workflows also need counts for staged, retried, rejected, and reconciled references. Integrity is an ongoing operational property, not a one-time migration result. Alerts should lead engineers to the responsible relationship and source quickly.

Common Foreign-Key Mistakes

  • Relying only on application checks and trusting every writer.
  • Choosing the wrong relationship direction or parent candidate key.
  • Using incompatible parent and child data types or collations.
  • Forcing optional relationships to NOT NULL or hiding them with fake IDs.
  • Assuming a foreign key makes child values unique or indexed.
  • Using CASCADE only to suppress a restrictive-delete error.
  • Expecting foreign keys to enforce active status or soft-delete behavior.
  • Adding constraints before cleaning historical orphans.
  • Disabling constraints for loads and failing to validate them afterward.
  • Ignoring locks, replication, ETL, CDC, and service ownership.

Foreign-Key Design Checklist

  • Identify the parent, child, relationship role, and cardinality.
  • Reference a stable PRIMARY KEY or appropriate UNIQUE candidate key.
  • Match data type, size, signedness, character set, and collation.
  • Decide whether the relationship is optional or mandatory.
  • Use UNIQUE on the child only when one-to-one behavior is required.
  • Define delete and update actions from lifecycle ownership.
  • Account for soft deletes, history, snapshots, and temporal rules.
  • Evaluate child indexes from real queries and parent changes.
  • Profile existing data and plan safe validation at production scale.
  • Document ORM, API, ETL, replication, and distributed boundaries.

Testing Foreign Keys

Test a valid child insert, invalid parent value, NULL for optional relationships, NULL rejection for mandatory relationships, child reassignment, parent key update, and every declared delete action. For composites, test complete matches, mismatched pairs, and partial NULLs.

Run concurrency tests around parent deletion and child insertion. Validate cascade row counts and transaction rollback. After migration, inspect schema metadata and confirm that the constraint is enabled, validated, and trusted by the optimizer where applicable.

Interview Questions and Answers

What is a foreign key? A column or column combination whose non-NULL values must match an allowed candidate key in a related parent table.

Can foreign-key values repeat? Yes. Repetition is expected in one-to-many relationships.

Can a foreign key be NULL? Yes, when the relationship is optional and the child columns permit NULL.

Can it reference UNIQUE instead of PRIMARY KEY? Commonly yes, provided the referenced candidate key satisfies the DBMS rules.

Does a foreign key create an index? Do not assume it creates a child index; behavior varies, and indexing should follow workload needs.

FOREIGN KEY versus JOIN? The constraint protects stored relationships; the join retrieves related rows.

What does CASCADE do? It propagates a parent delete or key update to dependent children according to the declaration.

Can a table reference itself? Yes, for hierarchies such as managers or categories, although extra rules may be needed to prevent cycles.

Key Takeaway

A foreign key is the database contract for a relationship. It protects every non-NULL child reference, coordinates parent changes through explicit actions, and prevents silent orphan creation. Strong design starts with entities, cardinality, candidate keys, NULLability, and lifecycle meaning rather than with DDL syntax alone.

RELIABLE FOREIGN KEY = CORRECT RELATIONSHIP DIRECTION
                     + UNIQUE STABLE PARENT KEY
                     + COMPATIBLE CHILD COLUMNS
                     + INTENTIONAL NULLABILITY
                     + SAFE REFERENTIAL ACTIONS
                     + WORKLOAD-AWARE INDEXING
                     + CLEAN EXISTING DATA
                     + TESTED PRODUCTION MIGRATION

Use database constraints with application validation, not instead of it. Keep integrity local to the database authority that owns the data, model distributed references explicitly, and treat cascade or validation changes as operational migrations with measurable consequences.