Foreign Key Constraints and Referential Actions (ON DELETE / ON UPDATE)

The data we'll use — category and item

A foreign key constraint (the constraint that guarantees a column's value always exists as a primary key in another table) is the mechanism that protects referential integrity between tables (the state where every referenced row actually exists).

It blocks contradictions like "an item points to a non-existent category" or "deleting a category leaves its items dangling," at the database layer.

Before the exercises, take a look at the column definitions and sample data for the two tables — category and item.

① Run PRAGMA table_info(category); and PRAGMA table_info(item); to check column definitions.

② Preview all rows with SELECT * FROM category; and SELECT * FROM item;.

③ Run PRAGMA foreign_keys; to see the current foreign-key check state (returned as a number).

Foreign-key checking is enabled with PRAGMA foreign_keys=ON

Foreign key constraints are declared by writing REFERENCES parent_table(parent_col) in the child table's column definition.

item.cat_id carries REFERENCES category(cat_id), which encodes the rule: every item.cat_id value must exist in category.

However, in this course's console the foreign-key check defaults to off (PRAGMA foreign_keys is 0).

While it's off, `REFERENCES` is not actually enforced and rows pointing to a non-existent category can be inserted.

To actually enforce the constraint, run PRAGMA foreign_keys=ON; once per connection.

-- Enable foreign-key checking
PRAGMA foreign_keys=ON;

-- Confirm the state (1 means enabled)
PRAGMA foreign_keys;

-- Try to insert a row pointing to cat_id=99 (not in category)
-- With foreign_keys=ON, this is rejected with FOREIGN KEY constraint failed
INSERT INTO item VALUES (5,'Mouse',99);

Imagine the requirement: "have the database block registration mistakes where an item references a non-existent category." With foreign-key checking enabled, you'll deliberately insert an inconsistent row and watch it error out. (Run it correctly and the explanation appears.)

① On line 1, run PRAGMA foreign_keys=ON; to enable the foreign-key check.

② Then INSERT a row into item with item_id 5, item_name Mouse, and cat_id 99 — a value that doesn't exist in category. This INSERT violates the foreign-key constraint, so an error is the correct outcome.

ON DELETE — what to do with child rows when a parent row is deleted

When a parent (category) row is deleted, what should happen to the child (item) rows that pointed to it? You decide by writing a referential action in the REFERENCES clause.

There are three common referential actions.

ON DELETE CASCADE means deleting the parent also deletes the children (cascade delete), ON DELETE SET NULL means the child's reference column is set to NULL and the row is kept, and ON DELETE RESTRICT means the parent delete itself is refused while children still reference it.

If you don't specify an action, the default is also RESTRICT-like (a referenced parent cannot be deleted).

Which behavior is appropriate depends on the business requirement.

-- Example declaration with ON DELETE SET NULL
CREATE TABLE cat_demo(cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL);
CREATE TABLE item_demo(
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  cat_id INTEGER REFERENCES cat_demo(cat_id) ON DELETE SET NULL
);
INSERT INTO cat_demo VALUES (1,'Stationery'),(2,'Electronics');
INSERT INTO item_demo VALUES (1,'Pen',1),(2,'Phone',2),(3,'Cable',2);

-- Deleting parent cat_id=2 turns Phone / Cable's cat_id to NULL
DELETE FROM cat_demo WHERE cat_id=2;
SELECT item_id, item_name, cat_id FROM item_demo ORDER BY item_id;

Imagine the requirement: "when a category is deleted, also delete every item that belongs to it." Build a self-contained parent/child with ON DELETE CASCADE and watch the parent delete cascade to the children.

① Enable the foreign-key check, then DROP TABLE IF EXISTS and recreate cat_x (cat_id, cat_name) and item_x (item_id, item_name, cat_id) where item_x.cat_id has an ON DELETE CASCADE foreign key into cat_x.

② Insert 2 rows into cat_x (Stationery / Electronics) and 3 rows into item_x (one with cat_id 1, two with cat_id 2).

③ Delete the category with cat_id 2 from cat_x, then SELECT all rows from item_x and confirm the items pointing to cat_id=2 were cascade-deleted.

SET NULL and RESTRICT — choose between keeping and refusing

ON DELETE SET NULL keeps the child row when the parent is deleted, just replacing the referencing column with NULL.

Use it when you don't want to delete the child record itself, like "keep the item as 'uncategorized.'"

If the child's reference column is NOT NULL, NULL can't be written, so columns used with SET NULL must allow NULL.

ON DELETE RESTRICT refuses the parent delete itself while children still reference it.

Use it when you want to fail safe (don't accidentally drop a master row that's still referenced).

Once you've deleted or reassigned all the children so no references remain, the parent can be deleted.

In the next exercise you'll set up SET NULL and RESTRICT side by side in one console to compare them.

-- RESTRICT example: parent with children can't be deleted
CREATE TABLE cat_r(cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL);
CREATE TABLE item_r(
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  cat_id INTEGER REFERENCES cat_r(cat_id) ON DELETE RESTRICT
);
INSERT INTO cat_r VALUES (1,'Stationery'),(2,'Electronics');
INSERT INTO item_r VALUES (1,'Pen',1);

-- cat_id=1 is referenced by item_r, so deletion is refused
DELETE FROM cat_r WHERE cat_id=1;

-- cat_id=2 has no references, so it can be deleted
DELETE FROM cat_r WHERE cat_id=2;
SELECT * FROM cat_r ORDER BY cat_id;

Imagine the requirement: "if we discontinue one category, keep its items as 'uncategorized' but don't touch the items in other categories at all." With 3 categories and 5 items under ON DELETE SET NULL, you'll see that only the discontinued parent's children become NULL while the others are untouched.

① Enable the foreign-key check, then DROP TABLE IF EXISTS and recreate cat_s (cat_id, cat_name) and item_s (item_id, item_name, cat_id) where item_s.cat_id has an ON DELETE SET NULL foreign key into cat_s. item_s.cat_id must allow NULL.

② Insert 3 rows into cat_s (Office=1 / Gadget=2 / Drink=3) and 5 rows into item_s (Marker and Tape in Office, Tablet and Charger in Gadget, Coffee in Drink).

③ Delete just Gadget (cat_id=2) from cat_s, then SELECT all rows from item_s and confirm that only Tablet and Charger's cat_id became NULL while the items under Office / Drink keep their original cat_id — all 5 rows still there.

Imagine the requirement: "prevent accidental deletion of a category that still has many items belonging to it." Build a self-contained parent/child with ON DELETE RESTRICT and watch it error out when you try to delete a parent that has 3 children.

① Enable the foreign-key check, then DROP TABLE IF EXISTS and recreate cat_t (cat_id, cat_name) and item_t (item_id, item_name, cat_id) where item_t.cat_id has an ON DELETE RESTRICT foreign key into cat_t.

② Insert Stationery (cat_id 1) into cat_t, and 3 items under it (e.g. Pen / Pencil / Notebook) into item_t.

③ Try to DELETE the category with cat_id 1 from cat_t. Since 3 children are still referencing it, an error is the correct outcome.