Skip to content
Quantoros

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

Related questions