A · Compressed recap
Four basic constraints: domain (values from the domain), key (candidate keys unique), entity integrity (PK never NULL), referential integrity (FK matches a PK or is NULL). Insert can break all four; delete only referential (deleting a referenced parent); updating a non-key attribute only domain; updating a PK all four; updating an FK domain and referential. RI breaks by inserting/changing a child FK or deleting/changing a referenced parent key.
On parent delete or PK update the FK's action decides: RESTRICT/NO ACTION rejects, CASCADE deletes (or updates) children and follows chains, SET NULL blanks direct children (impossible for PK attributes), SET DEFAULT puts the default.
B · Merged rule card
2.3.1 Domain, key, entity and referential integrity; which operations violate what
- Domain (values in domain), key (unique candidate keys), entity integrity (PK not NULL), referential integrity (FK matches a PK or is NULL).
- Insert: can violate all four. Delete: only referential (deleting a referenced parent). Update non-key: domain. Update PK: all four. Update FK: domain + referential.
- RI breaks by: inserting/updating a child FK, or deleting/updating a referenced parent PK.
- Insert into parent and delete from child are RI-safe.
2.3.2 Referential actions: restrict, cascade, set null; tracing cascading deletes
- Actions on parent delete/PK update: RESTRICT/NO ACTION (reject), CASCADE (delete/update children), SET NULL, SET DEFAULT.
- Cascades follow chains until nothing new refers to a deleted tuple.
- SET NULL changes direct children only; impossible for FKs in the PK or NOT NULL.
- RESTRICT on any affected reference rejects the whole delete.
C · Interleaved GATE-style questions
Q1 · MCQ · 1 mark(s) — Constraints
Inserting (NULL, 'Kiran') into STUDENT(roll PK, name) violates:
- (A) referential integrity
- (B) key constraint
- (C) entity integrity
- (D) domain constraint
Q2 · MSQ · 2 mark(s) — Operations
COURSE(cid PK, dept FK → DEPT). Which operations can violate referential integrity?
- (A) Insert into DEPT
- (B) Delete from DEPT
- (C) Delete from COURSE
- (D) Insert into COURSE
Q3 · NAT · 1 mark(s) — Counting
Which number of the four basic constraints can a delete operation violate?
Numerical answer: ________
Q4 · NAT · 2 mark(s) — Cascade
T(id, parent) with parent → T.id ON DELETE CASCADE: (1, NULL), (2, 1), (3, 1), (4, 3), (5, 3), (6, 5), (7, 2). Delete id 3. How many tuples are deleted?
Numerical answer: ________
Q5 · MCQ · 2 mark(s) — Actions
Same T, but ON DELETE RESTRICT. Delete id 7. What happens?
- (A) Sets 2's parent to NULL
- (B) Deletes 2 as well
- (C) Rejected, since 7 has a parent
- (D) Succeeds, since nothing references 7
Q6 · MCQ · 1 mark(s) — Actions
With ON DELETE SET NULL, deleting a parent:
- (A) sets the parent's key to NULL
- (B) sets direct children's FK to NULL and keeps them
- (C) deletes all descendants
- (D) is always rejected
Q7 · MCQ · 1 mark(s) — Update
Updating a foreign key attribute that is not part of any key can violate:
- (A) entity integrity only
- (B) domain and referential integrity
- (C) nothing
- (D) key constraint only
Q8 · MSQ · 2 mark(s) — Cascade
EMP(eid, mgr) with mgr → eid ON DELETE CASCADE: (1, NULL), (2, 1), (3, 2), (4, NULL), (5, 4). Delete employee 1. Which remain?
- (A) (2, 1)
- (B) (4, NULL)
- (C) (3, 2)
- (D) (5, 4)
D · Answer Vault
Reveal answers when you’re ready
Q1 Answer: (C) · 1 mark(s)
NULL primary key.
- (A) is wrong: No FK.
- (B) is wrong: No duplicate.
- (D) is wrong: NULL is not a domain issue here.
Re-read: Lesson 2.3.1 — Domain, key, entity and referential integrity; which operations violate what
Q2 Answer: (B) (D) · 2 mark(s)
Child insert, parent delete.
- (A) is wrong: Adding a parent is safe.
- (C) is wrong: Removing a child is safe (unless COURSE is itself referenced by another relation, which is not stated).
Re-read: Lesson 2.3.1 — Domain, key, entity and referential integrity; which operations violate what
Q3 Answer: 1 · 1 mark(s)
Only referential integrity.
Re-read: Lesson 2.3.1 — Domain, key, entity and referential integrity; which operations violate what
Q4 Answer: 4 · 2 mark(s)
3 → 4, 5; 5 → 6. Deleted: 3, 4, 5, 6.
Re-read: Lesson 2.3.2 — Referential actions: restrict, cascade, set null; tracing cascading deletes
Q5 Answer: (D) · 2 mark(s)
Restrict only matters if some tuple references the deleted one; 7 is a leaf.
- (A) is wrong: No.
- (B) is wrong: Cascades go child-ward, not parent-ward.
- (C) is wrong: References from 7 to its parent do not block deleting 7.
Re-read: Lesson 2.3.2 — Referential actions: restrict, cascade, set null; tracing cascading deletes
Q6 Answer: (B) · 1 mark(s)
One level, children kept.
- (A) is wrong: The parent is deleted.
- (C) is wrong: That is cascade.
- (D) is wrong: That is restrict.
Re-read: Lesson 2.3.2 — Referential actions: restrict, cascade, set null; tracing cascading deletes
Q7 Answer: (B) · 1 mark(s)
New value may be out of domain or reference nothing.
- (A) is wrong: Not a PK.
- (C) is wrong: It can.
- (D) is wrong: Not a key.
Re-read: Lesson 2.3.1 — Domain, key, entity and referential integrity; which operations violate what
Q8 Answer: (B) (D) · 2 mark(s)
1 → 2 → 3 deleted; the 4–5 chain is untouched.
- (A) is wrong: Deleted.
- (C) is wrong: Deleted with 2.
Re-read: Lesson 2.3.2 — Referential actions: restrict, cascade, set null; tracing cascading deletes