Foreign Key in SQL

Introduction

A foreign key is the database constraint that connects related rows and prevents those connections from pointing to records that do not exist. In an order system, an order normally belongs to a customer. Storing a customer identifier in the orders table records that relationship, while a foreign-key constraint verifies that the identifier matches an eligible key in the customers table.

This mechanism is central to relational design. Departments connect to employees, orders connect to order items, categories connect to products, and students connect to courses through enrollment rows. Without enforced references, an application can accidentally create orders for missing customers, line items for deleted orders, or payments for unknown invoices. Such rows are called orphans because the parent record they claim to belong to is absent.

A foreign key is simple to declare, but designing one correctly requires several decisions. The relationship may be optional or mandatory. Child values may repeat or require uniqueness. Deleting a parent may be rejected, cascaded, converted to NULL, or handled through a historical retention policy. Performance depends on appropriate indexes, and schema changes require careful ordering. Understanding these details turns a column reference into a reliable data-integrity contract.

What Is a Foreign Key?

A foreign key is a column or combination of columns in a referencing table whose non-null values must match values in a referenced key. The referenced key is commonly a primary key, although SQL databases can generally reference an appropriate UNIQUE candidate key as well. Exact eligibility and syntax vary by database product.

CUSTOMERS                          ORDERS
customer_id PRIMARY KEY  <------- customer_id FOREIGN KEY
customer_name                      order_id PRIMARY KEY

Here, orders.customer_id is the foreign key and customers.customer_id is the referenced key. The foreign key does not identify the order. The order's own primary key does that. The foreign key answers a different question: which customer is this order related to?

Why Foreign Keys Matter

Consider customers 101 and 102 and three orders whose customer values are 101, 101, and 102. Those values allow a query to find the owner of every order. More importantly, the constraint prevents customer 999 from being stored when customer 999 does not exist. The database enforces this rule for every application, import job, script, and concurrent session that writes to the table.

Application validation is still useful because it can return a friendly message, but it is not a substitute for a constraint. Two requests can race, a maintenance script can bypass one service, or another application can write directly. The database is the final shared boundary capable of enforcing referential integrity atomically.

Parent and Child Tables

The referenced table is often called the parent, and the table containing the foreign key is called the child. In a customer-to-orders relationship, customers is the parent and orders is the child. These labels describe the direction of the reference; they do not imply class inheritance or physical storage inside another row.

The parent key provides an eligible identity. The child stores a copy of that value as a reference. Many child rows can normally refer to one parent, which is why foreign-key values are allowed to repeat. Customer 101 can own orders 5001, 5002, and 5003 without violating the constraint.

Basic Foreign-Key Syntax

A table-level constraint clearly names the referencing and referenced columns. Creating the parent first makes the referenced key available when the child table is created.

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

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

Naming the constraint makes error messages and later schema changes easier to understand. If a migration must replace or drop it, fk_orders_customer is more useful than a product-generated name. Teams should choose a naming convention that remains unique within the scope required by their DBMS.

Inline and Table-Level Declarations

Some databases support an inline reference next to the column definition, while table-level syntax is portable and required for composite keys. Product behavior is not identical, so migration scripts should be verified against the actual engine rather than assumed from another dialect.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL
        REFERENCES customers (customer_id)
);

Even when concise inline syntax is available, an explicitly named table constraint can improve operational clarity. The important behavior is the enforced relationship, not the visual placement of the declaration.

Primary Key Versus Foreign Key

A foreign key can also participate in the child's primary key, but the constraints still perform distinct jobs. In an enrollment table, student_id and course_id may together form the primary key while each column separately references its parent. The combined key identifies one enrollment; the foreign keys validate the student and course.

Repeated Values Are Normally Valid

Foreign-key repetition is required for the common one-to-many relationship. If one department has 200 employees, its department_id appears in 200 employee rows. Adding UNIQUE to that foreign-key column would incorrectly limit the department to one employee.

INSERT INTO customers VALUES (101, 'John');

INSERT INTO orders VALUES (5001, 101, DATE '2026-01-10');
INSERT INTO orders VALUES (5002, 101, DATE '2026-01-11');
INSERT INTO orders VALUES (5003, 101, DATE '2026-01-12');

Date literal syntax varies among database systems, but the relationship principle remains the same. All three orders can reference customer 101 because a foreign key checks existence, not child-side uniqueness.

Optional and Mandatory Relationships

A nullable foreign-key column expresses an optional relationship. An employee may have no manager yet, so manager_id can be NULL. NULL means there is currently no referenced value; it does not need to match a parent key.

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

A mandatory relationship requires both NOT NULL and a foreign key. The NOT NULL rule requires a value, while the foreign key requires that value to identify an existing parent. Using only one constraint enforces only half of the business rule.

Referential Integrity and Orphan Prevention

Referential integrity means every non-null child reference corresponds to an eligible parent value. If customer 999 is absent, an order referencing 999 is rejected. This protects queries from silently returning incomplete relationships and prevents child data from losing its meaning.

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (5004, 999, CURRENT_DATE);

-- Rejected if customer 999 does not exist

A valid insertion sequence creates the parent before the child. Within a transaction, an application can insert the customer, retrieve or assign its key, insert the order, and commit both changes together. Some databases support deferred constraint checking for specific constraints, but deferral behavior is product-specific and should be used deliberately.

Multiple Foreign Keys and Roles

An order may reference a customer, billing address, shipping address, salesperson, and currency. Each relationship receives its own column or column group and constraint. Multiple foreign keys can also point to the same parent table in different roles.

CREATE TABLE shipments (
    shipment_id INT PRIMARY KEY,
    origin_address_id INT NOT NULL,
    destination_address_id INT NOT NULL,
    CONSTRAINT fk_shipment_origin
        FOREIGN KEY (origin_address_id) REFERENCES addresses (address_id),
    CONSTRAINT fk_shipment_destination
        FOREIGN KEY (destination_address_id) REFERENCES addresses (address_id)
);

Role-specific column and constraint names prevent ambiguity. A generic name such as address_id would not reveal whether it describes origin, destination, billing, or shipping.

Self-Referencing Foreign Keys

A foreign key does not have to reference another table. A self-reference models a hierarchy or network inside one entity type. The employee-manager example references another employee row, and a categories table may reference a parent category.

Self-references require lifecycle care. Deleting a manager may need to be rejected, set subordinate manager values to NULL, or reassign them. Hierarchical cycles may also be invalid business behavior, but an ordinary foreign key alone does not prevent employee A from referencing B while B eventually references A. Additional logic is required to enforce acyclic structures.

One-to-Many Relationships

For one customer with many orders, the foreign key belongs on the many side: orders. Putting order_id in customers cannot represent an arbitrary number of orders without repeating columns or storing a list, both of which violate relational design principles.

CUSTOMERS (one)
customer_id PRIMARY KEY
        |
        +---- ORDERS (many)
              customer_id FOREIGN KEY

The same rule applies to department-employees, category-products, and order-order_items. The child row stores the key of the single parent to which it belongs.

One-to-One Relationships

A foreign key by itself does not enforce one-to-one because the child value can repeat. Add UNIQUE on the child foreign key when each parent may have at most one child. For a user and profile model, profiles.user_id can be both UNIQUE and a foreign key.

CREATE TABLE profiles (
    profile_id INT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    biography VARCHAR(500),
    CONSTRAINT fk_profile_user
        FOREIGN KEY (user_id) REFERENCES users (user_id)
);

A shared-primary-key design can instead make user_id both the primary key and foreign key of profiles. This removes a separate profile identifier and strongly expresses dependent identity. Whether that is desirable depends on how the profile is addressed and whether its lifecycle is inseparable from the user.

Many-to-Many Relationships

A many-to-many relationship is implemented with a junction table. Enrollment connects students and courses by storing one foreign key to each parent. A composite primary key can prevent the same student-course pair from being inserted twice.

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrolled_on DATE NOT NULL,
    PRIMARY KEY (student_id, course_id),
    CONSTRAINT fk_enrollment_student
        FOREIGN KEY (student_id) REFERENCES students (student_id),
    CONSTRAINT fk_enrollment_course
        FOREIGN KEY (course_id) REFERENCES courses (course_id)
);

A junction table may instead use a surrogate enrollment_id, especially when other records must reference a particular enrollment or when repeat enrollment over time is valid. In that design, preserve the appropriate business uniqueness with a separate UNIQUE constraint, perhaps including term or enrollment sequence.

Composite Foreign Keys

A composite foreign key contains multiple columns and references a matching multi-column candidate key. Column order and data compatibility matter. All participating values collectively represent one reference.

CREATE TABLE warehouses (
    company_id INT NOT NULL,
    warehouse_code VARCHAR(20) NOT NULL,
    warehouse_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (company_id, warehouse_code)
);

CREATE TABLE inventory (
    company_id INT NOT NULL,
    warehouse_code VARCHAR(20) NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    CONSTRAINT fk_inventory_warehouse
        FOREIGN KEY (company_id, warehouse_code)
        REFERENCES warehouses (company_id, warehouse_code)
);

Composite NULL semantics can be subtle and vary with match options and database support. If the relationship is mandatory, declare every participating child column NOT NULL. This avoids partial references whose meaning is unclear.

Eligible Referenced Keys

The target must be a key that the database can use to identify an eligible parent row. A primary key is the most common target. A UNIQUE candidate key can also be appropriate, such as a stable country code or externally assigned account reference, subject to the database's rules about indexes, NULL values, and matching columns.

Do not reference a descriptive column merely because its current values happen to be distinct. Names, labels, and mutable codes can change or collide. Declare the candidate key formally and evaluate its stability before making many child tables depend on it.

Compatible Data Types

Referencing and referenced columns should use compatible types, lengths, signedness, character sets, and collations as required by the DBMS. An integer parent paired with a text child is a design error even if the text happens to contain digits. Mismatched string lengths or collations can prevent constraint creation or produce confusing comparisons.

Use the same domain definition for related identifiers wherever the platform supports it, or centralize schema conventions in migrations. Consistency also improves join performance and reduces conversion bugs in application code.

Parent Deletion and Referential Actions

A child reference makes parent deletion a design decision. SQL provides referential actions such as RESTRICT, NO ACTION, CASCADE, SET NULL, and in some products SET DEFAULT. Availability and timing differ among engines, so the chosen behavior must be tested on the target platform.

RESTRICT and NO ACTION are often similar in immediate checks, but the SQL standard and individual products can distinguish when enforcement occurs, especially with deferrable constraints. Treat them as dialect-sensitive rather than universal synonyms.

Using ON DELETE CASCADE Carefully

Cascade is appropriate when the child is compositionally owned by the parent. Deleting an order may reasonably delete its order items if those items cannot exist independently. It is dangerous when deletion has legal, financial, audit, or historical consequences.

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

Before enabling cascade, map the entire chain. Deleting one customer could cascade through orders, order items, shipments, and other tables. Large cascades can lock many rows, generate significant transaction logs, and remove more data than an operator expected. Tests should verify both the intended rows and records that must survive.

SET NULL, SET DEFAULT, and Updates

ON DELETE SET NULL preserves the child while removing its relationship. The child column must permit NULL, and an unassigned state must make sense. Preserving a support ticket after its optional assignee is deleted may be reasonable; preserving an order with no customer may violate the model.

SET DEFAULT requires a default that satisfies the foreign key. Pointing deleted relationships to a generic "unknown" row can help limited analytical models, but it can also obscure history. ON UPDATE CASCADE can propagate a changed parent key where supported. Stable surrogate keys reduce this need because internal identity should rarely change.

Foreign Keys and JOINs

A foreign key declares and enforces a relationship; a JOIN reads rows together. The constraint does not automatically join tables, and SQL can join columns even when no foreign key exists. Enforced relationships make join assumptions more trustworthy because every non-null child value is known to match a parent.

SELECT o.order_id,
       o.order_date,
       c.customer_name
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id;

Legacy systems sometimes rely only on logical relationships documented in code. That may be necessary across independent services or databases, but it shifts integrity responsibility to applications, reconciliation jobs, and monitoring.

Foreign Keys and Indexes

A foreign-key constraint and an index are different objects. The constraint validates references; an index accelerates access paths. Some engines automatically create or require supporting indexes in certain circumstances, while others do not automatically index child columns. Inspect the target database's schema and execution plans instead of relying on a general assumption.

Indexing the child foreign key often improves joins, parent deletion checks, updates, and queries such as "all orders for customer 101." For a composite foreign key, column order should support real query predicates as well as integrity operations. Every index adds storage and write cost, so design from workload evidence.

Write Performance and Concurrency

On INSERT or child-key UPDATE, the database checks whether the referenced parent exists. On parent DELETE or key UPDATE, it checks for dependent children and applies the configured action. These checks can take locks and access indexes, making schema and transaction design important under concurrency.

Transactions should generally create parents before children and delete children before parents when no cascade is configured. Keep transactions focused and use consistent operation ordering to reduce deadlock risk. Constraint failures should be treated as expected integrity signals, not suppressed without analysis.

Foreign Keys and TRUNCATE

TRUNCATE behavior around referenced tables varies significantly among products. A database may reject truncating a referenced parent, require truncating related tables together, support a cascade option, or apply restrictions even when the child is empty. TRUNCATE can also differ from DELETE in logging, triggers, identity handling, and rollback behavior.

Deployment and test-cleanup scripts must be written for the actual engine. Temporarily disabling foreign-key checks can leave corrupt data if re-enablement does not validate every existing row, so use that technique only with controlled validation and recovery procedures.

An E-Commerce Relationship Chain

An e-commerce model shows how references form a navigable chain. Customers own orders, orders own order items, and each item references a product. A payment can reference an order, while a shipment can reference an order and addresses. Each foreign key protects one local relationship.

CUSTOMERS
    |
    +-- ORDERS
          |
          +-- ORDER_ITEMS -- PRODUCTS
          +-- PAYMENTS
          +-- SHIPMENTS

A multi-table query can follow this chain, but the database does not infer every business rule from it. A foreign key can prove that a product exists; it cannot prove that the product was available, the recorded price matched the valid purchase-time price, or the shipment belongs to the same customer as the order. Those rules need additional constraints, transactional logic, or snapshots.

History, Audit Data, and Soft Deletes

Historical models often preserve the relationship while allowing descriptive parent attributes to change. An order may store customer_id for identity and snapshot the customer name or shipping address used at purchase time. The reference answers who the customer record is; the snapshot answers what facts applied to that transaction.

Effective-dated history may require a composite relationship such as department_id plus effective_from, or a separate immutable version identifier. Soft deletion keeps the parent row and marks it with deleted_at or a status flag. This preserves references but does not automatically hide deleted data or prevent new children from referencing an inactive parent. Queries, APIs, and additional business rules must enforce those semantics.

Foreign Keys in APIs

When an API receives {"customerId": 101} for a new order, it should validate permissions and business eligibility before attempting the insert. The database foreign key then guarantees that customer 101 exists at write time. A race in which the customer is deleted after an earlier application check is still caught by the database.

Map constraint failures to useful responses without exposing internal schema names or SQL text. A missing referenced resource may lead to a validation error or conflict depending on API semantics. Authorization remains separate: the fact that customer 101 exists does not prove the caller may create an order for that customer.

Adding a Foreign Key to Existing Data

A migration can fail when existing child values have no parent, types do not match, duplicate parent candidate values exist, or NULL conflicts with a new mandatory rule. Audit the data before adding the constraint.

SELECT o.customer_id, COUNT(*) AS orphan_count
FROM orders AS o
LEFT JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE o.customer_id IS NOT NULL
  AND c.customer_id IS NULL
GROUP BY o.customer_id;

Decide how to repair each orphan: create a legitimate parent, correct the reference, set it to NULL when optional, archive the row, or reject the migration until ownership is known. Do not attach all bad records to a fabricated parent merely to make the constraint pass.

Changing or Removing a Foreign Key

Changing a referenced key, nullability rule, or delete action is a data migration, not just a syntax edit. Inventory dependent tables and application assumptions, add supporting indexes, backfill data, validate in stages where the platform permits it, and coordinate deployment order so old and new application versions remain compatible.

Dropping a foreign key may be justified when data ownership moves across service boundaries or writes are intentionally asynchronous, but it removes a guarantee. Replace it with explicit validation, idempotent messaging, orphan detection, reconciliation, and alerting rather than leaving the relationship unowned.

Foreign Keys in Distributed Systems

A conventional foreign key normally cannot reference a row in another independent database or service. In microservice architectures, each service owns its data, and cross-service identifiers are logical references. Consistency may be eventual rather than transactional.

Within each service database, foreign keys remain valuable. Across boundaries, use contracts, durable events, retries, idempotency, status transitions, and reconciliation. Removing database constraints does not make a distributed model simpler; it transfers integrity work into operational processes.

Common Foreign-Key Mistakes

The most basic mistake is storing a parent identifier without declaring a constraint even though both tables share one relational database and the relationship should be strict. Other mistakes include referencing the wrong column, using incompatible types, allowing NULL for a mandatory relationship, and adding UNIQUE to a many-side foreign key.

Teams also apply cascade deletion without mapping its full effect, assume a foreign key automatically creates an index, or confuse the child reference with the child's identity. A foreign key can reference the same table, can contain multiple columns, and can form part of a primary key. None of these patterns changes its central purpose: validating a relationship.

Another mistake is expecting foreign keys to enforce every business rule. They cannot prove date ranges do not overlap, balances remain within limits, a manager chain has no cycle, or two referenced records belong to the same tenant unless those facts are represented by suitable keys and constraints.

Testing Foreign-Key Behavior

A useful test suite inserts a valid parent and child, attempts an invalid reference, verifies optional and mandatory NULL behavior, and confirms that repeated values work for one-to-many. One-to-one tests should prove that a second child for the same parent is rejected by UNIQUE. Composite tests should cover valid and mismatched combinations.

Lifecycle tests should exercise parent deletion and key updates for every configured action. Verify exactly which rows remain, change, or disappear. Include concurrent operations when the application can create and delete related records at the same time, and verify API error handling rather than only the database error.

-- Valid reference: should succeed
INSERT INTO orders VALUES (5001, 101, CURRENT_DATE);

-- Missing parent: should fail
INSERT INTO orders VALUES (5002, 999, CURRENT_DATE);

-- Mandatory relationship: should fail
INSERT INTO orders VALUES (5003, NULL, CURRENT_DATE);

A Foreign-Key Design Workflow

Start by defining what one row in each table represents. Identify the parent candidate key and the child that owns the relationship. Create compatible child columns, then decide whether the relationship is optional. Determine the cardinality: repetition for one-to-many, UNIQUE for one-to-one, or a junction table for many-to-many.

Next, choose deletion and update behavior from business lifecycle requirements. Consider history, audit, soft deletion, and distributed ownership. Add supporting indexes based on integrity operations and query workload. Name the constraint, clean existing data, deploy in a compatible sequence, and test valid references, invalid references, concurrency, and lifecycle actions.

Define row meaning
    -> identify parent candidate key
    -> place reference in child
    -> choose NULL or NOT NULL
    -> model cardinality
    -> choose delete/update action
    -> design supporting indexes
    -> validate existing data
    -> test integrity and lifecycle

Interview Questions and Answers

Can foreign-key values repeat? Yes. Repetition is normal on the many side of a one-to-many relationship. Add UNIQUE only when the relationship must be one-to-one.

Can a foreign key be NULL? Yes, if the child columns are nullable and the relationship is optional. Use NOT NULL with the foreign key for a mandatory relationship.

Must a foreign key reference a primary key? It commonly does, but it can generally reference an eligible UNIQUE candidate key. Exact requirements vary by database system.

Does a foreign key automatically create an index? Not universally. Some products create or require certain indexes, while others do not index child columns automatically. Inspect the schema and query plans.

What is the difference between a foreign key and a JOIN? A foreign key enforces integrity during writes. A JOIN combines related rows during a query. Either can exist without the other.

Where does the foreign key go in one-to-many? It goes on the many side. Each child stores the identifier of its one parent.

Can a table reference itself? Yes. Self-referencing keys model relationships such as employee-manager or category-parent category.

Practical Review Checklist

• Define the parent and child row meanings before creating the relationship.
• Reference a stable primary or eligible UNIQUE candidate key.
• Use compatible types, lengths, signedness, collations, and column order.
• Use NOT NULL when every child must have a parent.
• Allow repeated child values for one-to-many relationships.
• Add UNIQUE when the child relationship must be one-to-one.
• Model many-to-many relationships through a junction table.
• Choose delete and update actions from real lifecycle requirements.
• Map complete cascade chains before enabling automatic deletion.
• Evaluate a supporting child index using the target DBMS and workload.
• Clean orphaned legacy data before adding the constraint.
• Test valid, invalid, NULL, repeated, composite, and deletion behavior.

Key Takeaway

A foreign key connects a child row to an existing parent key and lets the database enforce referential integrity. It can repeat, can be nullable for an optional relationship, can contain multiple columns, and can reference another row in the same table. Its meaning is different from a primary key: the primary key answers "who am I?" while the foreign key answers "which row am I related to?"

PRIMARY KEY
    = identity of this row

FOREIGN KEY
    = validated reference to a related row

UNIQUE
    = no duplicate candidate values

The strongest design does more than add syntax. It models cardinality and optionality correctly, chooses deliberate lifecycle actions, preserves history where required, supports operations with appropriate indexes, and verifies behavior through migrations and tests. For the most common one-to-many relationship, remember the practical rule: the foreign key belongs on the many side.