A foreign key requires a child reference to match an allowed parent key, with referential actions defining what happens when that parent is updated or deleted.
A saved customer_id should point to a real customer whenever a sale is required to have an owner. Foreign keys enforce that relationship in the database. Deletion policy is a separate decision: deleting a customer should not accidentally erase financial history.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How Foreign keys and referential actions works
The child table stores the reference, and the parent table supplies a primary or otherwise eligible unique key. A sale.customer_id foreign key to customers.id checks the relationship when records are inserted or changed. A nullable reference can permit an absent relationship, such as an anonymous sale, unless NOT NULL requires one. The exact parent-key eligibility and indexing requirements vary, but the relationship must identify one valid parent rather than an arbitrary nonunique label.
ON DELETE and ON UPDATE specify actions for parent changes. CASCADE propagates a deletion or key update to matching children. SET NULL removes the saved reference and therefore needs nullable child columns. RESTRICT and NO ACTION block invalid changes with timing differences in engines that support deferral. Defaults and enforcement details differ, so state the intended policy explicitly instead of assuming one action is always the universal default.
Choosing CASCADE, SET NULL or RESTRICT
A cascade is convenient for dependent data that has no meaning without its parent, such as disposable association records. It can be inappropriate for historical transactions whose retention is required by the business. Soft deletion or an archival model can preserve those records while preventing new activity. Cascades may traverse several relationships, so review the entire dependency graph before choosing them. A tiny DELETE statement can affect many child rows.
Foreign keys do not automatically index every child reference in every engine. An appropriate child-side index can help joins and parent-change checks, but choose it according to actual lookup needs. In SQLite, foreign-key enforcement must normally be enabled on each connection with PRAGMA foreign_keys = ON, outside an active transaction when changing that setting. PostgreSQL supports deferrable constraints for some workflows; MySQL does not offer the same deferred checking. Test invalid inserts as well as valid ones so a relationship definition is known to be enforced rather than merely present in a schema file.
MySQL, PostgreSQL, SQL Server and SQLite
The displayed PRAGMA is SQLite-specific and should be omitted in PostgreSQL, MySQL and SQL Server. The table relationship and SET NULL concept exist across them, but supported actions and deferral rules differ. MySQL commonly uses InnoDB for enforced foreign keys. PostgreSQL can defer eligible checks; SQL Server and MySQL do not share all of those options.
Check the engine-specific syntax and version requirements in SQLite foreign-key enforcement.
Practice task and worked solution
Using the existing customers and sales data, find sale records whose customer link is absent. Do not confuse an absent link with a link to an invalid non-NULL id.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
SELECT id,customer_id FROM sales
WHERE customer_id IS NULL ORDER BY id;id | customer_id
106 | NULLSale 106 has no saved customer link. A nullable foreign key can allow that state. To detect invalid non-NULL references in an unconstrained legacy table, use a separate anti-join and an IS NOT NULL filter; it is a different data-quality category from this anonymous record.
On the enforced practice relationship, attempt to insert a non-NULL customer id that has no parent and expect rejection. Insert a NULL reference and confirm it is allowed by the nullable field. Compare that state with the SET NULL result after deleting the practice parent. In SQLite check the connection’s foreign_keys setting before trusting the invalid-reference test.
SQL Foreign Keys: Referential Integrity, CASCADE and SET NULL Syntax
Foreign keys and referential actions
-- Enable foreign keys on this SQLite connection.
PRAGMA foreign_keys = ON;
CREATE TABLE referenced_customers (id INTEGER PRIMARY KEY);
CREATE TABLE referenced_sales (
id INTEGER PRIMARY KEY,customer_id INTEGER,
FOREIGN KEY(customer_id) REFERENCES referenced_customers(id) ON DELETE SET NULL
);
INSERT INTO referenced_customers (id) VALUES (1);
INSERT INTO referenced_sales (id,customer_id) VALUES (1,1);
DELETE FROM referenced_customers WHERE id=1;
SELECT id,customer_id FROM referenced_sales;SQL Foreign Keys: Referential Integrity, CASCADE and SET NULL Summary
- A foreign key requires each child reference to match an existing parent key, or to be
NULLif the column allows it. ON DELETE CASCADEdeletes child rows with their parent;SET NULLkeeps them and clears the reference;RESTRICTorNO ACTIONblocks the delete.- Choose the action from the business rule: historical records such as sales usually shouldn't be cascaded away.
SET NULLonly works when the child column is nullable.- SQLite enforces foreign keys only after
PRAGMA foreign_keys = ONon each connection.
SQL Foreign Keys: Referential Integrity, CASCADE and SET NULL Examples
Each example shows the code, the output it produces and what each part does.
ON DELETE SET NULL keeps the child row
This self-contained SQLite relationship uses SET NULL. Deleting the practice parent preserves the child sale but removes its now-invalid reference. The child column is nullable, so the action has a valid destination state. The common customers and sales tables are not deleted.
-- Enable foreign keys on this SQLite connection.
PRAGMA foreign_keys = ON;
CREATE TABLE referenced_customers (id INTEGER PRIMARY KEY);
CREATE TABLE referenced_sales (
id INTEGER PRIMARY KEY,customer_id INTEGER,
FOREIGN KEY(customer_id) REFERENCES referenced_customers(id) ON DELETE SET NULL
);
INSERT INTO referenced_customers (id) VALUES (1);
INSERT INTO referenced_sales (id,customer_id) VALUES (1,1);
DELETE FROM referenced_customers WHERE id=1;
SELECT id,customer_id FROM referenced_sales;id | customer_id
1 | NULLCommon SQL Foreign Keys: Referential Integrity, CASCADE and SET NULL Mistakes
SET NULL on a NOT NULL column, and unenforced keys
Defining ON DELETE SET NULL on a NOT NULL child field creates an incompatible deletion outcome. Another mistake is assuming SQLite enforces a foreign key simply because REFERENCES appears in CREATE TABLE. Enable and verify enforcement on each connection, and insert a deliberately invalid child in a controlled test.
SQL Foreign Keys: Referential Integrity, CASCADE and SET NULL in Real Projects
Account removal and sale history
A customer account system can preserve historical sale records while choosing whether account removal should be blocked or unlink the owner. This is a requirements and retention decision. Put the chosen relationship rule in the schema so every writer follows it, rather than hoping each application route implements identical cleanup logic.
Choose deletion actions from retention requirements, not convenience. Disposable assignments may cascade with their parent, while historical transactions often need preservation or blocked deletion. Review multi-level dependencies because one parent deletion can propagate widely. An optional relationship needs nullable child fields, whereas a mandatory relationship also needs NOT NULL.
SQL Foreign Keys: Referential Integrity, CASCADE and SET NULL FAQ
Does a foreign key make the child field required?
No. A foreign key only checks values that are present; a NULL reference is allowed unless the column is also NOT NULL. Add NOT NULL if a child must always have a parent, such as an order line that always belongs to an order.
Does CASCADE mean archive?
No. A deletion cascade permanently removes matching child rows; it does not preserve history elsewhere. If records must be kept, block the delete with RESTRICT, use a soft-delete flag on the parent, or copy rows to an archive table first.
Does every engine create a child index?
No. MySQL's InnoDB creates an index on the foreign key column automatically, but PostgreSQL and SQLite don't. Check the engine's behaviour and design the appropriate index deliberately, because deletes on the parent and joins both look up child rows by that column.
What is ON UPDATE CASCADE for?
It changes the child references automatically when a parent key value changes, such as a product code being renamed. With surrogate ids that never change, it's rarely needed. Oracle doesn't support ON UPDATE CASCADE, and SQL Server limits cascade paths, so avoid relying on key updates.
Can a foreign key reference a column that isn't the primary key?
Yes, if that column has a UNIQUE constraint, such as customers.email or a product sku. The referenced columns must uniquely identify one parent row, otherwise the database can't tell which parent a child belongs to.
SQL Foreign Keys: Referential Integrity, CASCADE and SET NULL Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
What remains after the SET NULL example deletes its practice parent?
SQL Foreign Keys: Referential Integrity, CASCADE and SET NULL Quiz
2 questions to check what you learned.
What does ON DELETE SET NULL do to a matching child?
- Deletes every matching child row
- Keeps the child row and clears its reference
- Rejects the parent change regardless of child nullability
What is normally required for SQLite foreign-key enforcement?
- Only declaring a column whose name ends in _id
- Issuing the PostgreSQL CREATE DATABASE command first
- Enable PRAGMA foreign_keys on the connection