Foreign-key questions become tricky when reference direction, parent-child roles or cascading deletes are reversed. Use six checks: key placement, parent-child direction, referential-integrity operations, self-reference, cascading actions, and the difference between DDL constraints and query behaviour. Choose before reading explanations; for two relations, draw the child-to-parent arrow. KnowledgeGate offers more than 60 practice questions on Foreign Key and Referential Integrity. This topic sits within CS Fundamentals.
Foreign key MCQs 1-3: purpose, identification and placement
A foreign key sits on the child or many side; each non-null value must match a permitted parent key, usually primary, though some DBMSs permit another candidate or unique key.
Question 1
Purpose of 'Foreign Key' in a table is to ensure
A. Null Integrity
B. Referential Integrity
C. Domain Integrity
D. Null and Domain Integrity
Answer: B. Referential Integrity. Orders.customer_id = 42 needs parent Customers.customer_id = 42, unless the child may be NULL. Domain and nullability rules eliminate A, C and D.
This question is from Indian Space Research Organization 2009, Computer Science.
Question 2
Consider the two relations below. The primary keys are underlined. Identify all possible foreign key(s) from the options based only on the two relations.
EMP (eid, ename, did)
DEPT (did, dname)
A. eid
B. did
C. eid, did
D. eid, did, ename
Answer: B. did. EMP.did -> DEPT.did. EMP.eid is primary and ename lacks a referenced-key counterpart, eliminating A, C and D.
This question is from UGC NET 2022, Computer Science, December.
Question 3
Assume a one-to-many relationship from table named "authors" to the table named "books". Where should the foreign key reside in an optimal design for this relationship?
A. In the "books" table
B. In the "authors" table
C. In both the tables
D. In a separate linking table
Answer: A. In the "books" table. Author (7, 'Asha') serves (101, 7) and (102, 7), so books.author_id references it. B reverses direction, C duplicates the key, and D suits many-to-many.
This question is from Indian Space Research Organization 2025, Computer Science.
Referential integrity MCQs 4-5: operations that can violate the rule
Parent insert and child delete are safe; child insert and referenced-parent delete can fail. Given R2.x = {3, 7} and R1.a = {3, 7}, child a = 9 and parent deletion x = 7 fail; parent x = 9 and child deletion a = 3 are safe.
Question 4
Which scenario demonstrates a violation of referential integrity?
A. Having duplicate EmployeeID values in an Employees table
B. Setting Age = -5 in an Employees table
C. Inserting ProductID = 'P999' into an Orders table where 'P999' doesn't exist in Products
D. Storing a Name as NULL in a Customers table
Answer: C. P999 has no parent in Products. A is key uniqueness, B a domain rule, and D nullability.
This question is from Beltron Programmer 2025, Computer Science, Shift-2.
Question 5
Let R1 (a, b, c) and R2 (x, y, z) be two relations in which a is the foreign key of R1 that refers to the primary key of R2 . Consider following four options. (a) Insert into R1 (b) Insert into R2 (c) Delete from R1 (d) Delete from R2 Which of the following is correct about the referential integrity constraint with respect to above ?
A. Operations (a) and (b) will cause violation.
B. Operations (b) and (c) will cause violation.
C. Operations (c) and (d) will cause violation.
D. Operations (d) and (a) will cause violation.
Answer: D. Operations (d) and (a). Child insert R1.a = 9 can fail; deleting parent R2.x = 7 can orphan child 7. Parent inserts and child deletes are safe; see DBMS Transaction MCQs.
This question is from UGC NET 2018, Computer Science, July.
Self-referencing foreign key MCQs 6-7
Parent and child may share a relation. In Employee(emp_id, manager_id), (10, NULL), (20, 10), (30, 20) make manager_id reference emp_id; a relation may have several foreign keys.
Question 6
Consider the following statements S₁ and S₂ about the relational data model:
S₁: A relation scheme can have at most one foreign key.
S₂: A foreign key in a relation scheme R cannot be used to refer to tuples of R.
Which one of the following choices is correct?
A. Both S₁ and S₂ are true
B. S₁ is true and S₂ is false
C. S₁ is false and S₂ is true
D. Both S₁ and S₂ are false
Answer: D. Both S₁ and S₂ are false. Several foreign keys disprove S₁. In (10, NULL), (20, 10), (30, 20), each non-null manager resolves in Employee, disproving S₂.
This question is from GATE 2021, Computer Science, Set 2.
Question 7
A recursive foreign key is a:
A. references a relation
B. references a table
C. references its own relation
D. references a foreign key
Answer: C. references its own relation. Employee-to-manager is one example. It targets a candidate or primary key, so D is wrong.
This question is from UGC NET 2007, Computer Science, June.
ON DELETE CASCADE MCQ 8: trace every dependent row
Trace each deleted key. Deleting (20,40) removes key 20, then (35,20); key 35 removes (30,35). Nothing references 30, so the additional set is {(35,20), (30,35)}.
Question 8
Following table has two attributes Employee_id and Manager_id, where Employee_id is a primary key and manager_id is a foreign key referencing Employee_id with on-delete cascade:
Employee – Manager Table
Employee_id | Manager_id |
|---|---|
20 | 40 |
25 | 40 |
30 | 35 |
35 | 20 |
40 | 45 |
45 | 25 |
On deleting the table (20,40), the set of other tuples that must be deleted to maintain the referential integrity of table is
A. (30,35) only
B. (30,35) and (35,20) only
C. (35,20) only
D. (40,45) and (25,40) only
Answer: B. (30,35) and (35,20) only. Delete (35,20), then dependent (30,35). Rows (25,40) and (40,45) reference other values; D traces backwards.
This question is from UGC NET 2019, Computer Science, June.
Integrity constraint and DDL MCQs 9-10
Constraint | What it protects |
|---|---|
Entity integrity | Primary-key attributes are non-null, and the key uniquely identifies each row. |
Domain integrity | Values allowed in a column. |
Referential integrity | Valid inter-row or inter-table references. |
User-defined integrity | Additional business rules. |
Question 9
Match the following with respect to RDBMS :
List I | List II |
|---|---|
(a) Entity integrity | (i) enforces some specific business rule that do not fall into entity or domain |
(b) Domain integrity | (ii) Rows can’t be deleted which are used by other records |
(c) Referential integrity | (iii) enforces valid entries for a column |
(d) Userdefined integrity | (iv) No duplicate rows in a table |
Code :
A. (a)-(iii); (b)-(iv); (c)-(i); (d)-(ii)
B. (a)-(iv); (b)-(iii); (c)-(ii); (d)-(i)
C. (a)-(iv); (b)-(ii); (c)-(iii); (d)-(i)
D. (a)-(ii); (b)-(iii); (c)-(iv); (d)-(i)
Answer: B. Domain integrity gives (b)-(iii), user-defined integrity gives (d)-(i), and referential integrity gives (c)-(ii). The question uses (a)-(iv) as the intended match for entity integrity. Strictly, entity integrity requires every primary-key attribute to be non-null; key uniqueness, not entity integrity alone, prevents duplicate primary-key values.
This question is from UGC NET 2017, Computer Science, November.
Question 10
Drop Table cannot be used to drop a Table referenced by _______ constraint. (a) Primary key (b) Sub key (c) Super key (d) Foreign key
A. (a)
B. (a), (b) and (c)
C. (d)
D. (a) and (d)
Answer: C. (d) Foreign key. A parent drop is rejected until its child dependency is handled; other key labels create no dependency. DROP ... CASCADE is DBMS-specific; compare DBMS SQL Query MCQs.
This question is from UGC NET 2015, Computer Science, June.
Foreign key MCQs 11-12: joins are not constraints, consistency is
A JOIN needs no foreign-key declaration; the constraint enforces references. With parent {42}, Orders(501, 99) is rejected because 99 is absent.
Question 11
Assertion (A): We can retrieve records from more than one table in MYSQL. Reason (R): Foreign key is used to establish a relationship between two tables.
A. Both (A) and (R) are true and (R) is the correct explanation of (A)
B. Both Assertion (A) and Reason (R) are true and Reason (R) is not the correct explanation for Assertion (A)
C. (A) is true but (R) is false
D. (A) is false but (R) is true
Answer: B. Both are true but describe different mechanisms. MySQL can join Orders.customer_id to Customers.customer_id without a foreign key; the constraint protects references.
This question is from Bihar STET 2025, Computer Science.
Question 12
How does a properly implemented foreign key constraint contribute to the consistency property in a database transaction?
A. By ensuring that all inserted rows are committed immediately
B. By ensuring that a referenced record exists in the parent table
C. By automatically removing orphan records during insertion
D. By allowing duplicate values in the referencing column
Answer: B. With only parent 42, Orders(501, 99) is rejected. Immediate commit, orphan cleanup and duplicate child values are unrelated.
This question is from Beltron Programmer 2025, Computer Science, Shift-2.
Foreign key question patterns and decision checks
Pattern | Questions |
|---|---|
Definition and placement | 1-3 |
Invalid parent-child operations | 4-5 |
Self-reference and cascade | 6-8 |
Constraint and query semantics | 9-12 |
Check the parent, child and arrow; repeat each deleted cascade key. Replay 20 -> 35 -> 30, then use DBMS Normalization MCQs for keys, dependencies and normal forms.
Foreign key MCQs: the short version and next step
Foreign keys live on the referencing side; each non-null child value must resolve to a permitted parent key. Insert-child and delete-parent are risky; recursive references are valid, and cascades move through dependents. Redo Questions 5, 6 and 8 for direction, self-reference and recursive cascade. ZERO TO HERO provides the wider DBMS sequence.
