What is referential integrity?
SQLLevel: IntermediateDatabase Design
What is referential integrity?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
Referential integrity means that foreign key values must match existing primary key values in the referenced table.
-- ON DELETE options:
-- RESTRICT: reject delete if children exist (default)
-- CASCADE: delete children when parent is deleted
-- SET NULL: set FK to NULL when parent is deleted
CREATE TABLE order_items (
item_id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(order_id) ON DELETE CASCADE,
product_id INT REFERENCES products(product_id) ON DELETE RESTRICT
);
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT