← 🧱 ER Model, Relational Model & Keys
Subtopic 5 of 5

1.5Integrity constraints and referential actions

The idea

Integrity constraints keep data valid. Entity integrity: no primary key part is NULL. Referential integrity: a foreign key must match an existing key or be NULL.

Think of it like this: A library loan card must name a real member; you cannot lend a book to nobody, and you cannot delete a member who still has books out (unless you also cancel the loans).

Why it exists

Questions ask which operation can violate the constraint and what each ON DELETE option does.

How it works

  1. Insert into the child table: the foreign key must exist in the parent (or be NULL).
  2. Delete or update in the parent: the child rows would be left dangling.
  3. Choose the reaction: NO ACTION/RESTRICT rejects, CASCADE follows, SET NULL clears, SET DEFAULT resets.
  4. Only the parent table can be harmed by delete; only the child by insert.

Worked example: Delete department d1 that 2 employees reference, ON DELETE CASCADE.

Dept d1 and both employee rows are deleted.

Now play with it

  • →Run each operation below with each action.
  • →Find the operation that is always accepted (insert with NULL foreign key).
Dept (referenced)
dno (PK)dname
d2ECE
d3ME
Emp (referencing)
eid (PK)enamedno (FK → Dept)
3Chend2
4DiaNULL

d1 deleted and the 2 referencing Emp rows are deleted too.

Only the referenced table can cause violations on delete or update; the referencing table can only be blocked on insert or when its foreign key is updated to a missing value.

Check yourself

Which operation on the CHILD (referencing) table can violate referential integrity?

Remember: Insert into child and delete/update in parent are the dangerous operations.
Exam notes (the dense version)

Entity integrity: no part of a primary key is NULL. Referential integrity: a foreign key value must match an existing key in the referenced table, or be NULL. Violations come from: inserting a bad foreign key, or deleting / updating a referenced key. Actions on the referenced table: NO ACTION / RESTRICT (reject), CASCADE, SET NULL, SET DEFAULT.

Previous year questions: ER Model, Relational Model & Keys (9)

PYQs are grouped by topic, so this is the full set for ER Model, Relational Model & Keys; try the ones that match this subtopic. Open all 9 on the topic page.