What Is Referential Integrity?

Referential integrity^[1] requires database references to point to existing rows. For a single-column foreign key, every non-NULL value must match a value in the referenced key. An enforced foreign-key constraint prevents a child row from referring to a parent that does not exist.

In this example, CUSTOMER_ORDER.CustID references CUSTOMER.CustID. A customer may have zero or many orders, but every order must reference exactly one customer. The foreign key checks that the customer exists; NOT NULL requires an order to supply the customer ID.

CUSTOMER stores customer facts; CUSTOMER_ORDER stores order facts and the customer identifier. CUSTOMER.CustID is the primary key, CUSTOMER_ORDER.OrderNo is the order primary key, and CUSTOMER_ORDER.CustID is the foreign key. CUSTOMER_ORDER avoids the reserved SQL keyword ORDER.

A foreign key can reference a primary key or another eligible unique key. The parent and child can also be the same table, as when an employee references their manager. Matching column names alone do not enforce a relationship.

Three rules have distinct responsibilities: the parent key provides a unique target; the foreign key requires a matching target; NOT NULL requires a value. None of these requires every customer to have an order. Multiple orders can reference customer 101 because the child CustID is not constrained to be unique.

PRAGMA foreign_keys = ON;

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_order_customer
        FOREIGN KEY (CustID) REFERENCES CUSTOMER(CustID)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
);

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

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

-- Rejected: no customer 9999
INSERT INTO CUSTOMER_ORDER (OrderNo, CustID, OrderDate)
VALUES (5002, 9999, '2026-10-15');

-- Rejected: cannot redirect an order to a missing customer
UPDATE CUSTOMER_ORDER
SET CustID = 9999
WHERE OrderNo = 5001;

-- Rejected under ON DELETE NO ACTION
DELETE FROM CUSTOMER WHERE CustID = 101;

-- Rejected under ON UPDATE NO ACTION
UPDATE CUSTOMER SET CustID = 102 WHERE CustID = 101;

An orphan record is a child row that references a missing parent. An INNER JOIN can omit such a row, hiding it from a report; the join itself does not repair the data. A LEFT JOIN can help expose missing matches when investigating legacy data.

Database enforcement protects writes from applications, imports, scripts, and users while the constraint is active. Application checks alone can miss writes from another path or race with concurrent changes. Existing invalid data must be handled when introducing or validating a constraint; simply drawing a relationship does not clean it up.

Actions when a referenced parent is deleted or its key changes

Action	Effect
NO ACTION	Rejects a violation when the constraint is checked. Timing depends on whether the database and constraint allow deferred checking.
RESTRICT	Blocks the referenced operation without deferring the restriction. It is not interchangeable with NO ACTION in every system.
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	Clears the child reference. The affected columns must allow NULL; it is incompatible with the mandatory CustID in this example.
SET DEFAULT	Assigns a default value, where supported. The resulting reference must still satisfy the foreign key.

Choose each action deliberately. For this lesson, NO ACTION preserves orders and blocks deletion of their customer. CASCADE is not an archive or anonymization mechanism: deleting a customer would also delete referencing orders. Supported actions and checking timing vary by platform.

NOT NULL and a foreign key do different jobs. This column declaration requires a value, but needs the separately declared foreign key to require an existing customer:

CustID INTEGER NOT NULL

For a single-column optional relationship, a nullable foreign key allows no assigned parent. For example, these are definition fragments for a design that already has a SalesRep table with a suitable key:

SalesRepID INTEGER NULL,
FOREIGN KEY (SalesRepID) REFERENCES SalesRep(SalesRepID)

A NULL SalesRepID has no representative reference to check. A non-NULL value must match an existing representative. Composite foreign keys have additional NULL-matching rules; do not generalize the single-column explanation without checking the database's supported matching behavior.

A foreign key is a schema constraint; a join is a query operation. A join does not require a declared foreign key, and declaring a foreign key does not automatically join tables. After the examples above, this query returns the unchanged valid order:

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

Referential integrity proves that customer 101 exists, not that customer 101 is the correct customer for order 5001. It does not validate the customer's name, an order date, or authorization to place an order. Those requirements need their own constraints and application rules.

For composite references, corresponding child columns reference a parent key as a combination. Column count, mapping order, and compatible types matter. The underlying principle remains that every reference requiring a match must have a valid target.

Can a customer have no orders? Yes. Can an order use customer 9999 when that customer does not exist? No. Can customer 101 be deleted while order 5001 references it? Not under this example's NO ACTION policy. The foreign key protects the reference; the chosen referential actions determine how parent changes are handled.

[1] Referential integrity: The requirement that foreign-key references requiring a match identify existing referenced rows.