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

Updated 16 Nov 20265 min read
Foreign Key and Referential Integrity in DBMS: Worked Examples and Exam Traps

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.

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

sql
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:

  1. A non-NULL child value with a parent match is valid.

  2. Repeated child values are valid because several students may belong to one department.

  3. NULL is valid only if the child column permits it. It asserts no reference.

  4. 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 (105, Esha, 30)

30 exists

Yes

Row 105 enters Student.

2

Insert (106, Farhan, 40)

40 is absent

No

No row 106 enters, so the state stays unchanged.

3

Insert (107, Gita, 10)

10 exists

Yes

Row 107 enters. Asha and Gita may both use 10.

4

Change Bharat from 10 to 20

20 exists

Yes

Row 102 now contains 20; the other rows do not change.

5

Delete Department 10

10 exists, but rows 101 and 107 refer to it

No

The parent row remains, and both child rows remain unchanged.

6

Delete Department 30

30 exists, but row 105 refers to it

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.

Department and Student rows before step 5: arrows from Asha, Bharat, Charu, Esha and Gita to their departments, Dev's NULL unlinked.

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 checked NO ACTION, rejects the delete. Department 10 and Students 101 and 107 remain.

  • ON DELETE CASCADE removes Department 10 and Student rows 101 and 107.

  • ON DELETE SET NULL removes Department 10 but leaves rows 101 and 107 with dept_id = NULL. The child column must be nullable.

Deleting department 10 three ways: RESTRICT rejects it, CASCADE removes it with students 101 and 107, SET NULL blanks their dept_id.

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

  1. Foreign keys must be unique. False. Asha and Gita may both reference Department 10. Uniqueness belongs to the referenced candidate key.

  2. A missing parent is like NULL. False. 40 asserts a reference and fails without parent 40; NULL asserts none. Test NULL, then membership in the parent-key set.

  3. Only child inserts matter. Parent deletes and key updates can create dangling references. Test every surviving non-NULL child value.

  4. 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:

  1. Find the referenced candidate-key columns.

  2. List the current parent-key values.

  3. For every surviving non-NULL child tuple, test exact membership.

  4. 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.