Lesson 14 of 15 · 22 min
Referential integrity
By the end of this lesson you can detect orphaned rows, clean them up, add a foreign key to an existing table (directly or with NOT VALID and VALIDATE), change a key's ON DELETE behaviour, and order inserts so parents exist before children.
Referential integrity means that every reference points at something that exists: every orders.customer_id matches a customer, every order_items.product_id matches a product. A row whose reference points nowhere is an orphan.
In lesson 4 you declared foreign keys on new tables. A foreign key enforces integrity from then on, in both directions:
- inserting or updating a child row with a reference that does not exist is rejected;
- deleting or re-keying a parent row that still has children is handled by the key's
ON DELETE/ON UPDATEaction: rejected (NO ACTION, the default, orRESTRICT), passed on (CASCADE), or the children are detached (SET NULL,SET DEFAULT).
This lesson is about the messier situations: data that arrived before the key existed, keys whose behaviour must change, and scripts that insert related rows in the wrong order.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons