Relational Integrity and Schema Design

When a referenced (parent) tuple is deleted or its key updated, referential integrity must be maintained via one of: CASCADE (propagate the delete/update to child tuples), SET NULL (set the child's foreign key to NULL — requires FK be nullable), SET DEFAULT (set FK to a predefined default), and RESTRICT/NO ACTION (reject the operation if dependent child tuples exist). Default in most systems is NO ACTION/RESTRICT. Memory aid: CASCADE = follow the parent; SET NULL = orphan but flagged; RESTRICT = block. ON DELETE CASCADE can chain through multiple levels. Choosing CASCADE carelessly risks unintended mass deletions; RESTRICT is the safest conservative choice.

NULL means 'unknown' or 'not applicable', NOT zero or empty string. Key pitfalls for GATE: (1) NULL = NULL evaluates to UNKNOWN, not TRUE — use IS NULL. (2) Aggregate functions ignore NULLs (except COUNT(*) which counts all rows). (3) Three-valued logic: AND/OR/NOT over {TRUE, FALSE, UNKNOWN}. TRUE OR UNKNOWN = TRUE; FALSE AND UNKNOWN = FALSE; TRUE AND UNKNOWN = UNKNOWN. (4) A WHERE clause passes a row only if the condition is TRUE (UNKNOWN rows are dropped). (5) UNIQUE constraints generally allow multiple NULLs, but PRIMARY KEY allows none. Shortcut: any arithmetic with NULL yields NULL; any comparison with NULL yields UNKNOWN.

With T=TRUE, F=FALSE, U=UNKNOWN: AND -> T AND U = U, F AND U = F, U AND U = U. OR -> T OR U = T, F OR U = U, U OR U = U. NOT U = U. Quick rule: in AND, F dominates (F with anything = F); in OR, T dominates (T with anything = T); U appears only when no dominating value is present. For WHERE/HAVING/JOIN-ON, only rows evaluating to TRUE qualify; both FALSE and UNKNOWN rows are excluded. For CHECK constraints the opposite leniency applies: a CHECK passes unless it evaluates to FALSE (UNKNOWN is accepted). Remember this asymmetry: WHERE needs TRUE; CHECK only rejects FALSE.

Cover the answer, recall, then check. 12 cards on relational integrity constraints and schema design (GATE).

Q1. State the entity integrity constraint.
A1. No attribute of a primary key may be NULL in any tuple. Rationale: a NULL primary-key value could not uniquely identify the tuple.

Q2. State the referential integrity constraint.
A2. Every non-NULL foreign-key value must equal some existing value of the referenced primary/candidate key. FKs may be NULL only if the column allows it.

Q3. What is a domain constraint?
A3. Each attribute value must belong to that attribute's declared domain (correct type/range), and must be atomic.

Q4. What is a key (uniqueness) constraint?
A4. No two tuples may have the same value on a candidate key. Declared via PRIMARY KEY or UNIQUE.

Q5. Difference between PRIMARY KEY and UNIQUE constraints?
A5. A table has one PRIMARY KEY (no NULLs allowed); it may have several UNIQUE constraints, and a UNIQUE column typically permits one NULL (SQL standard treats NULLs as distinct).

Q6. List the referential triggered actions on delete/update of a referenced tuple.
A6. CASCADE (propagate the change), SET NULL, SET DEFAULT, and NO ACTION / RESTRICT (reject if dependents exist).

Q7. What does ON DELETE CASCADE do?
A7. Deleting a referenced (parent) tuple automatically deletes all child tuples whose foreign key referenced it, preserving referential integrity.

Q8. Name the three modification anomalies from poor design.
A8. Insertion anomaly (can't add data without unrelated data), deletion anomaly (removing a tuple loses unrelated facts), and update anomaly (a repeated fact must be changed in many places, risking inconsistency).

Q9. Which constraints are checked on an INSERT?
A9. Domain, NOT NULL, key/UNIQUE, entity integrity (PK not NULL), referential integrity (FK exists), and any CHECK constraints — all must hold for the insert to succeed.

Q10. What is a CHECK constraint?
A10. A predicate on a column/row that every tuple must satisfy (e.g. CHECK (age >= 0)); an insert or update violating it is rejected.

Q11. Can a foreign key reference a column in its own table?
A11. Yes — a self-referencing (recursive) foreign key, e.g. Employee.manager_id referencing Employee.emp_id.

Q12. Deleting a parent row with ON DELETE SET NULL — effect on children?
A12. The children's foreign-key attribute is set to NULL (the FK column must be nullable), keeping the rows but removing the dangling reference.

Integrity constraints are rules the DBMS enforces so that stored data stays valid. GATE tests both the definitions and the consequences — which operations violate which constraint, and what triggered actions repair referential integrity.

The constraint families

Constraint	Rule	Enforced via
Domain	Values atomic and from the declared domain	column type, CHECK
Key / uniqueness	No two tuples share a candidate-key value	PRIMARY KEY, UNIQUE
Entity integrity	Primary-key attributes are never NULL	PRIMARY KEY
Referential integrity	Non-NULL FK matches an existing referenced value	FOREIGN KEY

Entity integrity is why a primary key cannot be NULL. Referential integrity links relations: a foreign key must either be NULL (if allowed) or match a live primary/candidate-key value — no "dangling" references.

Triggered actions

When a referenced tuple is updated or deleted, the child rows can be repaired by a declared action: CASCADE (propagate delete/update to children), SET NULL, SET DEFAULT, or NO ACTION / RESTRICT (reject the operation while dependents exist).

Schema design and anomalies

A badly designed (redundant) schema suffers three anomalies: insertion (cannot record one fact without another), deletion (removing a row erases an unrelated fact), and update (a duplicated fact must be updated everywhere at once). Normalization removes these by decomposing on functional dependencies — the payoff that motivates 2NF/3NF/BCNF.

Exam Tricks & Tips

• 🎯 An INSERT can violate domain, key, entity-integrity, referential-integrity, and CHECK constraints; a DELETE of a parent typically threatens only referential integrity (of children).
• 🎯 PRIMARY KEY = UNIQUE + NOT NULL. A table has at most one PRIMARY KEY but any number of UNIQUE constraints.
• 🎯 UNIQUE usually allows one NULL (NULLs are treated as distinct); PRIMARY KEY allows none.
• 🎯 ON DELETE CASCADE can chain: deleting one parent may recursively delete grandchildren through a chain of cascading FKs.
• 🎯 A foreign key can reference a candidate/UNIQUE key, not only the primary key, and can be self-referencing within the same table.
• ❌ Common mistake: thinking referential integrity forbids NULL foreign keys — a NULL FK is allowed (it simply means "no reference"); only a non-NULL FK must match.

Expected exam pattern

1–2 marks: "which of these operations violates a constraint", tracing the effect of ON DELETE CASCADE/SET NULL, or identifying insertion/deletion/update anomalies in a given schema as motivation for normalization.

Quick recap

Four constraint families: domain, key, entity integrity (PK ≠ NULL), referential integrity (FK matches or is NULL). Repair FK violations with CASCADE / SET NULL / SET DEFAULT / RESTRICT. Redundant schemas cause insertion, deletion, and update anomalies — the reason we normalize.

Worked Example

Problem: Given the schema Employee(emp_id PK, name, dept_id FK → Department.dept_id) and Department(dept_id PK, dname), explain the entity integrity and referential integrity constraints, and give one operation that would violate each.

Solution:
Entity integrity: the primary key of a relation must be unique and cannot be NULL (so each tuple is identifiable).
Violation example: INSERT INTO Employee with emp_id = NULL, or inserting a second employee whose emp_id duplicates an existing one — both break entity integrity.

Referential integrity: a foreign-key value must either match an existing primary-key value in the referenced relation or be NULL (no dangling references).
Violation example: INSERT INTO Employee(emp_id=5, name='A', dept_id=99) when no Department with dept_id = 99 exists. Also, DELETE-ing a Department row that some Employee still references (via dept_id) violates referential integrity unless an ON DELETE CASCADE/SET NULL action is defined.

Answer: Entity integrity forbids null/duplicate primary keys; referential integrity requires each foreign key to match an existing primary key or be null. Inserting a NULL emp_id violates the first; inserting dept_id = 99 with no such department (or deleting a referenced department) violates the second.