What is a Foreign Key?

A foreign key^[1] is a column or group of columns constrained to reference a primary key or another eligible unique key. The reference may point to another table or to the same table, as when an employee references their manager. The table that contains the foreign key is called the child table, and the table being referenced is called the parent table.

The purpose of a foreign key is to define and enforce a relationship between tables. It tells the relational database management system that non-NULL values in a single-column foreign key must correspond to existing referenced values. This rule is part of referential integrity, which prevents child rows from pointing to parent rows that do not exist.

A foreign key is not merely a column that happens to have the same name as a column in another table. Matching column names can make a design easier to read, but the declared constraint is what creates the formal relationship. The database designer defines the relationship, and the RDBMS enforces it through the foreign key constraint.

CUSTOMER(CustID, CustLast, CustFirst)

CUSTOMER_ORDER(OrderNo, CustID, OrderDate)

A foreign key relationship has two sides. The parent table contains the referenced key. The child table contains the foreign key. In the CUSTOMER and CUSTOMER_ORDER example, CUSTOMER is the parent table because it contains the primary key CustID. CUSTOMER_ORDER is the child table because it contains a foreign key named CustID.

The rule can be stated this way: every non-null value in CUSTOMER_ORDER.CustID must match an existing value in CUSTOMER.CustID. If the database contains a customer with CustID 101, then an order can reference CustID 101. If there is no customer with CustID 9999, then the database should reject an order that attempts to use CustID 9999.

CUSTOMER
CustID = 101

CUSTOMER_ORDER
OrderNo = 5001
CustID  = 101

This order belongs to customer 101. The CUSTOMER_ORDER row does not repeat the customer's last name or first name. It stores the customer identifier, and the database can join CUSTOMER_ORDER to CUSTOMER when the full customer information is needed.

A foreign key prevents an orphan row: a child row whose reference has no matching parent. It checks references during inserts and updates, and applies the configured action when a referenced parent is deleted or its key changes. It cannot determine whether an existing customer is the correct customer for a particular order.

The following example creates CUSTOMER and CUSTOMER_ORDER. The name CUSTOMER_ORDER avoids the SQL keyword ORDER. These examples use simple SQL supported by SQLite with foreign-key enforcement enabled; other systems may require platform-specific setup. In SQLite, run PRAGMA foreign_keys = ON; before starting a transaction.

CREATE TABLE CUSTOMER (
    CustID      INTEGER PRIMARY KEY,
    CustLast    VARCHAR(50) NOT NULL,
    CustFirst   VARCHAR(50) NOT NULL
);

CREATE TABLE CUSTOMER_ORDER (
    OrderNo    INTEGER PRIMARY KEY,
    CustID     INTEGER NOT NULL,
    OrderDate  DATE NOT NULL,

    CONSTRAINT fk_customer_order_customer
        FOREIGN KEY (CustID)
        REFERENCES CUSTOMER(CustID)
        ON DELETE NO ACTION
);

The primary key of CUSTOMER is CustID. The primary key of CUSTOMER_ORDER is OrderNo. The foreign key constraint says that CUSTOMER_ORDER.CustID must reference a valid value in CUSTOMER.CustID.

The constraint name fk_customer_order_customer is optional in some database systems, but naming constraints is a good design practice. A named constraint is easier to identify when the database reports an error or when a DBA needs to modify the schema.

After creating the tables, add customer 101 and two orders for that customer:

INSERT INTO CUSTOMER (CustID, CustLast, CustFirst)
VALUES (101, 'Rivera', 'Elena');

INSERT INTO CUSTOMER_ORDER (OrderNo, CustID, OrderDate)
VALUES (5001, 101, '2026-10-15');

INSERT INTO CUSTOMER_ORDER (OrderNo, CustID, OrderDate)
VALUES (5002, 101, '2026-10-15');

Both orders are valid. A foreign key need not be unique: CustID 101 repeats in CUSTOMER_ORDER, while each OrderNo is unique. Adding UNIQUE to the child CustID would instead limit each customer to at most one order.

Run this next statement separately as an intentional failure. If customer 9999 does not exist, the foreign key rejects it:

INSERT INTO CUSTOMER_ORDER (OrderNo, CustID, OrderDate)
VALUES (5003, 9999, '2026-10-15');

The rejected statement does not create order 5003. Error messages and transaction recovery differ by database system.

In a one-to-many relationship, one row in the parent table can be associated with many rows in the child table. A customer may have zero, one, or many orders; every order in this design belongs to exactly one customer.

The foreign key usually appears on the many side of the relationship. CUSTOMER_ORDER is the many side because many order rows may reference the same customer row. CUSTOMER is the one side because each CustID identifies one customer.

This does not mean the customer data is copied into every order. Only the foreign key value is stored in the CUSTOMER_ORDER table. The rest of the customer data remains in the CUSTOMER table. This reduces redundancy and keeps customer facts in one place.

A foreign key can be mandatory or optional. A mandatory foreign key requires a value. An optional foreign key allows NULL.

This example explicitly requires a customer for every order, so the child column is declared:

CustID INTEGER NOT NULL

NOT NULL requires a value; the FOREIGN KEY constraint requires that value to identify an existing customer. Neither constraint requires every customer to have an order. Other applications may choose different rules for guest orders.

Optional relationships are also common. For example, an order might have an optional SalesRepID if a sales representative can be assigned later:

SalesRepID INTEGER NULL

This fragment declares only a nullable column. A separate FOREIGN KEY (SalesRepID) REFERENCES SalesRep(SalesRepID) constraint would establish the relationship, assuming that parent table and key exist. NULL would permit no assigned representative; any non-NULL value would still need a matching parent. Composite foreign keys have additional NULL-matching rules that vary by system.

When a parent row is referenced by child rows, the database needs a rule for what happens when the parent row is deleted or its key value is updated. These rules are called referential actions.

Common referential actions include:

• RESTRICT or NO ACTION: Reject an operation that would leave invalid references when the constraint is checked. Support and checking timing differ by database; PostgreSQL allows a deferrable NO ACTION check to be deferred, while RESTRICT cannot be deferred.
• CASCADE: ON DELETE CASCADE deletes referencing child rows; ON UPDATE CASCADE propagates changes to the referenced key into child foreign-key values, where supported.
• SET NULL: Set the child foreign key value to NULL when the parent row is deleted or changed, if NULL is allowed.

For a customer-order database, deleting a customer who has order history should usually be restricted unless the organization has a deliberate archive, anonymization, or cascade policy. In many business systems, preserving order history is more important than physically deleting the customer row.

Foreign keys and joins are related, but they are not the same thing. A foreign key is a schema constraint. A join is a query operation.

A foreign key protects the validity of the relationship. A join combines rows according to its condition and does not require a declared foreign key. Conversely, a foreign key does not automatically join tables in a query.

SELECT
    c.CustID,
    c.CustLast,
    c.CustFirst,
    o.OrderNo,
    o.OrderDate
FROM CUSTOMER c
JOIN CUSTOMER_ORDER o
    ON o.CustID = c.CustID;

This query returns customer and order data together. The join condition uses the relationship between CUSTOMER.CustID and CUSTOMER_ORDER.CustID. The foreign key constraint helps ensure that each order points to a valid customer.

A foreign key column and the referenced primary key column do not have to use the same name. Matching names are common because they make diagrams and SQL easier to read, but names alone do not define the relationship.

The following is an alternative table definition using OrderedByID. It replaces the earlier CUSTOMER_ORDER definition; do not run it as another creation step in the same schema:

CREATE TABLE CUSTOMER_ORDER (
    OrderNo      INTEGER PRIMARY KEY,
    OrderedByID  INTEGER NOT NULL,
    OrderDate    DATE NOT NULL,

    FOREIGN KEY (OrderedByID)
        REFERENCES CUSTOMER(CustID)
);

OrderedByID is still a foreign key because it references CUSTOMER.CustID. The declared constraint establishes the relationship.

A foreign key can reference more than one column. This is called a composite foreign key. Composite foreign keys are used when the referenced key is made from multiple columns.

For example, if an order-line table is identified by the combination of OrderNumber and ItemNumber, another table could reference both columns together:

FOREIGN KEY (OrderNumber, ItemNumber)
    REFERENCES OrderLine(OrderNumber, ItemNumber)

The child columns must correspond to the referenced key columns in number, order, and compatible data types. The combination is checked as a pair, not as two independent references. This connects foreign keys to earlier lessons about concatenated primary keys and all-key relations.

A foreign key is a column or group of columns constrained to reference a primary key or another eligible unique key in a referenced table, which may be the same table. It defines a relationship between a child table and a parent table. In the CUSTOMER and CUSTOMER_ORDER example, CUSTOMER_ORDER.CustID is a foreign key that references CUSTOMER.CustID.

Foreign keys enforce referential integrity by preventing invalid references. They help ensure that an order cannot be assigned to a customer that does not exist. They also make relationships between tables explicit in the database schema.

The most important idea is that a foreign key is a declared database constraint, not merely a matching column name. The database designer defines the relationship, and the relational database management system enforces it.

Before you move on to the next lesson, click the Quiz link below to reinforce your understanding of foreign keys.
Relational Constructs - Quiz

[1] Foreign key: A column or group of columns constrained to reference a primary key or another eligible unique key, including within the same table.