Foreign key; referencing vs referenced relations

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. R(A, B, C, D) has candidate keys {A} and {B}. How many superkeys? from Lesson 2.2.1
2. Can a primary key attribute be NULL? from Lesson 2.2.1
3. In a 1:N relationship, which entity can absorb a relationship attribute? from Lesson 1.2.2

1 · The Hook

ENROLL(roll, cid, grade) says "student 7 took course C9". But what if there is no student 7, or no course C9? The row would describe enrolments of people who do not exist. Tables need a way to say: "a roll in ENROLL must be a roll that exists in STUDENT". That rule is a foreign key.

2 · Predict First

ENROLL.roll refers to STUDENT.roll. Which of these inserts into ENROLL should the DBMS reject: (5, C1, A) when no student 5 exists; (1, C1, A) when student 1 exists; (NULL, C1, A)?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

(5, C1, A) must be rejected: it refers to a student who does not exist. (1, C1, A) is fine. A NULL foreign key would normally be allowed ("refers to nobody yet"), but here roll is also part of ENROLL's primary key, and primary key attributes cannot be NULL, so this insert is rejected too, for a different reason.

Definition

A set of attributes FK in relation R₁ is a foreign key referencing relation R₂ if:

1. FK has the same domain(s) as the primary key PK of R₂ (names may differ), and
2. in every valid state, each tuple t₁ of R₁ either has t₁[FK] = t₂[PK] for some tuple t₂ of R₂, or t₁[FK] is NULL.

• R₁ is the referencing (child) relation; R₂ is the referenced (parent) relation.
• In diagrams, draw an arrow from FK to the PK it references.

Facts GATE checks

• A foreign key references a primary key (some systems also allow any candidate key declared unique).
• The FK attribute names need not match: STUDENT.dept → DEPT.dname is fine.
• A relation can have several foreign keys: ENROLL.roll → STUDENT, ENROLL.cid → COURSE.
• A foreign key can be part of the primary key: in ENROLL, roll and cid are each a foreign key, and {roll, cid} together is the primary key.
• A relation can reference itself: STUDENT(roll, name, mentor_roll) where mentor_roll → STUDENT.roll (a self-referencing foreign key).
• A foreign key value may be NULL (unless it is also part of the primary key or declared NOT NULL).
• Foreign key values may repeat: many students in dept CS.

Database instance with foreign keys

STUDENT (dept → DEPT.dname)

DEPT

Every dept value in STUDENT appears as a dname in DEPT. ME has no students, which is allowed: the rule only constrains the referencing side.

4 · Visual

Arrows run from each foreign key to the primary key it references. ENROLL has two foreign keys, which together form its primary key.

5 · Worked Examples

Example 1 Easy

In ENROLL(roll, cid, grade) with roll → STUDENT and cid → COURSE, name the referencing and referenced relations.

Solution

Referencing: ENROLL. Referenced: STUDENT and COURSE.

Example 2 Medium

COURSE(cid, title, prereq) where prereq → COURSE.cid. Is (C9, AI, C1) a valid insert if C1 exists? Is (C10, Intro, NULL) valid?

Solution

Both are valid. prereq is a self-referencing foreign key; C1 exists, and NULL means "no prerequisite".

Example 3 GATE

STUDENT(roll, dept) has dept → DEPT(dname). DEPT holds {CS, EE}. Which inserts into STUDENT succeed: (7, CS), (8, ME), (9, NULL), (7, EE)?

Solution

(7, CS) succeeds. (8, ME) fails: ME is not in DEPT. (9, NULL) succeeds (NULL foreign key allowed, dept is not part of the key). (7, EE) fails, but because of the primary key (roll 7 already exists), not the foreign key.

8 · Connect the Dots

Lesson 2.2.1 picked the primary key; this lesson let other tables point to it with foreign keys. Next: these are two of the four basic integrity constraints, and each kind of update (insert, delete, update) can break some of them and not others.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Direction

In ENROLL(roll, cid) with roll referencing STUDENT(roll), the referenced relation is:

1. (A) neither
2. (B) STUDENT
3. (C) both
4. (D) ENROLL

Q2 · MSQ · 2 mark(s) — Properties

Which are true of foreign keys?

1. (A) A relation may have a foreign key referencing itself.
2. (B) A foreign key may also be part of the primary key of its relation.
3. (C) A foreign key must have the same name as the key it references.
4. (D) A foreign key value may be NULL unless forbidden by another constraint.

Q3 · MCQ · 1 mark(s) — Validity

DEPT holds dname values {CS, EE}. Which STUDENT(roll, dept) tuple violates the foreign key dept → DEPT.dname?

1. (A) (5, NULL)
2. (B) (7, EE)
3. (C) (4, CS)
4. (D) (6, ME)

Q4 · MCQ · 2 mark(s) — Keys

In ENROLL(roll, cid, grade), roll and cid are foreign keys and {roll, cid} is the primary key. Which insert is rejected even if the student and course exist?

1. (A) (1, C3, B) when no (1, C3) row is present
2. (B) (2, C1, A) when no (2, C1) row is present
3. (C) (3, C1, NULL) when no (3, C1) row is present
4. (D) (1, C2, A) when (1, C2, B) is already present

Q5 · NAT · 1 mark(s) — Counting

STUDENT has 120 students in 4 departments. DEPT has 6 departments. How many DEPT tuples are referenced by no student, at least?

Numerical answer: ________

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

foreign key (FK)referencing (child) relationreferenced (parent) relationself-referencing foreign keyFK as part of PK