Foreign Key and Referential Integrity in DBMS: Worked Examples and Exam Traps
Learn how foreign keys keep child rows consistent with parent keys. Trace six operations, compare referential actions, and avoid common exam traps.
KnowledgeGate Team
Exam prep & CS education

A foreign key is easy to spot in a table definition. The harder exam question asks whether a particular insert, update, or delete leaves the database legal. You must decide which child values need matching parent values, then apply the action declared for a referenced-row change. In the running Department and Student database, each accepted change becomes the baseline for the next check. The same method applies throughout GATE CS preparation.
1. Foreign key and referential integrity are related, not identical
A foreign key is one or more child-relation attributes constrained to reference a candidate key in a parent relation. That candidate key is often the primary key, but need not be. The child refers to the parent, although the parent stores no child list.
Referential integrity is the database-state rule that every non-NULL foreign-key value must match an existing referenced-key value. The foreign key is the constraint; referential integrity describes states satisfying it.
Entity integrity forbids NULL in a primary-key component. A domain constraint controls allowed values or types. Referential integrity controls matching across relations. A foreign key need not be unique, and not every key is a foreign key.
The running schema is Department(dept_id, dept_name) and Student(student_id, name, dept_id).
2. Read the SQL constraint as a precise matching rule
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)The first clause identifies the child column. REFERENCES identifies the parent relation and column. Their data types must be compatible, and checking uses the current database state.
The broader Constraints and Keys in SQL lab combines PRIMARY KEY, UNIQUE, CHECK and FOREIGN KEY failures. This trace isolates one foreign key so each accepted state change can be followed without other constraints changing the result.
Start with four rules:
A non-NULL child value with a parent match is valid.
Repeated child values are valid because several students may belong to one department.
NULLis valid only if the child column permits it. It asserts no reference.An unmatched non-NULL value is invalid.
In one-to-many mapping, the key from the one side becomes a foreign key on the many side. ER Model to Relational Mapping for GATE covers that process. Immediate versus deferred checking is DBMS-specific.
3. Worked example: test each insert, update and delete in order
Begin with these tables. Student.dept_id is nullable, and the foreign key uses restrictive behaviour with no cascade or set-null action.
dept_id | dept_name |
|---|---|
10 | CSE |
20 | ECE |
30 | ME |
student_id | name | dept_id |
|---|---|---|
101 | Asha | 10 |
102 | Bharat | 10 |
103 | Charu | 20 |
104 | Dev | NULL |
Apply each accepted operation before the next.
Step | Proposed change | Parent match before change | Accepted? | State consequence |
|---|---|---|---|---|
1 | Insert |
| Yes | Row 105 enters |
2 | Insert |
| No | No row 106 enters, so the state stays unchanged. |
3 | Insert |
| Yes | Row 107 enters. Asha and Gita may both use |
4 | Change Bharat from |
| Yes | Row 102 now contains |
5 | Delete Department |
| No | The parent row remains, and both child rows remain unchanged. |
6 | Delete Department |
| No | The parent row remains, and Esha's child row remains unchanged. |
The final parent-key set is {10, 20, 30}. In student-id order 101, 102, 103, 104, 105, 107, the child foreign-key multiset is {10, 20, 20, NULL, 30, 10}. Rejected operations leave the state unchanged.

4. What ON DELETE and ON UPDATE actions actually change
Compare separate alternatives from the state before step 5. They are not applied in sequence.
RESTRICT, or immediately checkedNO ACTION, rejects the delete. Department 10 and Students 101 and 107 remain.ON DELETE CASCADEremoves Department 10 and Student rows 101 and 107.ON DELETE SET NULLremoves Department 10 but leaves rows 101 and 107 withdept_id = NULL. The child column must be nullable.

For a parent-key update from 20 to 25, ON UPDATE CASCADE changes Department 20 and both child references, Bharat's and Charu's, to 25. Restrictive behaviour rejects it, leaving all three values at 20. SET NULL needs nullable child columns and DBMS support. A SET DEFAULT result must still satisfy the foreign key.
Do not make RESTRICT and NO ACTION universal synonyms. Follow the question's semantics, or the chosen DBMS documentation for timing and support.
5. Composite, self-referencing and NULL cases that change the check
Let Offering(course_id, term) have candidate key (course_id, term), referenced by Enrollment(student_id, course_id, term). Given parents (DBMS, 2026S) and (OS, 2026S), child pair (DBMS, 2026S) matches, but (DBMS, 2026F) does not. The pair is tested together, not across different rows.
In self-reference, one relation plays both roles. If Employee.manager_id references Employee.emp_id, (1, NULL) can represent the top manager and (2, 1) is valid. (3, 9) is invalid without employee 9.
A NULL foreign key asserts no reference, so matching is skipped when NULL is permitted. For composite keys, use the match mode stated in the question instead of assuming a universal partial-NULL rule.
6. Common traps and the correction for each
Foreign keys must be unique. False. Asha and Gita may both reference Department 10. Uniqueness belongs to the referenced candidate key.
A missing parent is like NULL. False.
40asserts a reference and fails without parent 40;NULLasserts none. Test NULL, then membership in the parent-key set.Only child inserts matter. Parent deletes and key updates can create dangling references. Test every surviving non-NULL child value.
Normalization guarantees referential integrity. A BCNF schema can still have an orphan. Normalization in DBMS: 1NF to BCNF handles dependency-based decomposition; foreign keys enforce cross-table references.
7. How exams test referential integrity
Questions may ask you to identify a foreign key, choose a legal state, judge an operation, calculate a referential action, or distinguish integrity constraints.
Use this four-check method:
Find the referenced candidate-key columns.
List the current parent-key values.
For every surviving non-NULL child tuple, test exact membership.
Apply the declared action before judging the final state.
Run the four checks on all six operations, including the rejected changes that leave the state untouched.
8. The short version and the next step
A legal state matches every surviving non-NULL foreign-key tuple to a parent tuple. Child duplicates may be legal. Parent changes need either unbroken references or a declared repair action.
Re-solve steps 1 to 6 with ON DELETE CASCADE, then ON DELETE SET NULL, and write the rows. For structured preparation, use GATE Guidance by Sanchit Sir. Draw the parent-key set and test every proposed survivor.
Keep learning
Foreign Key and Referential Integrity MCQs: 12 Solved Questions with Explanations
Solve 12 published foreign key MCQs with clear explanations of parent-child direction, recursive references, cascading deletes and database consistency.
Keys and Integrity Constraints in DBMS: One Cross-Concept Worked Example
Follow one college-registration database from functional dependencies to declared keys, then decide which of eight inserts and deletes preserve integrity.
Keys and Integrity Constraints in DBMS: Types, Rules and Worked Examples
Learn how superkeys, candidate keys, primary keys, foreign keys and integrity rules work together through one university database and a complete closure proof.
Introduction to Normalization and 1NF in DBMS: Worked Table and Exam Traps
Follow Asha and Ravi's fictional StudentRecord data from collection-valued cells to two valid 1NF designs. See where cross products, anomalies, keys, and dependencies remain.





