# Primary Keys, Foreign Keys & Data Integrity

- **Track:** Cambridge IGCSE™ Computer Science (0478 / 0984) (CAMBRIDGE IGCSE 0478)
- **Module:** Section 9: Relational Databases & SQL
- **Estimated Duration:** 10 minutes
- **Access:** Open Theory (All-Access for Interactive IDE & Autotests)

---

## Theory & Conceptual Background

A primary key uniquely identifies each record in a table. Its values must not be duplicated or missing. A person's name is usually unsuitable because different people can share a name. A unique MemberID is more dependable.

## Worked example

Two records have Name="Alex Lee" but MemberID values 104 and 105. The keys are unique even though the names match. A key constraint prevents duplicate key values; it does not prove the two records describe different real people.

Validation checks whether input meets a rule, not whether it is true. A presence check can require a name, a range check can limit age, and a format check can check the layout of an identifier. A validly formatted wrong date may still pass.

## Independent design

Choose a primary key for a school library's books when several copies share a title. Explain why a separate copy identifier is needed. Choose a validation rule for a publication year and explain one incorrect value that could still pass it.

This course's core database tasks use a single table. Multiple-table relationships and advanced key taxonomy are optional later study.

---

## Interactive Exercises & Autotest Specifications

### Exercise 1: Exam-Style Check: Primary Key Constraints and Selection (Multiple choice, 25 XP)

**Instructions:**
A database table stores medical records for 5,000 hospital patients. The designer considers using the patient's `FullName` as the primary key.

Why is `FullName` unsuitable as a primary key, and what field should be used instead?

**Options:**
- **A**: FullName is too long to be indexed by modern database software.
- **B**: DateOfBirth should be used instead of FullName because birthdays never change.
- **C**: FullName cannot guarantee uniqueness because multiple patients may share the same name; a unique PatientID number must be used instead.
- **D**: FullName always contains NULL values in hospital databases.


**Autotest Expectations:**
- **Primary key uniqueness requirement and selection identified**: `selection_match` -> Expected: `opt_patient_id`

### Exercise 2: Exam-Style Question: Select Primary Key and Validation Rule (Fill in blank, 35 XP)

**Instructions:**
A medical clinic database stores a `Patients` table with the following fields:

- `NationalID` (a unique 10-digit citizen identifier)
- `Surname`
- `DateOfBirth`
- `BloodGroup` (must be one of 'A', 'B', 'AB', 'O')

1. The field that should be chosen as the primary key is: [blank_1]
2. To ensure that `BloodGroup` only contains one of the four accepted blood types, the database should apply a [blank_2] check.
3. To ensure that `NationalID` contains exactly 10 characters, the database should apply a [blank_3] check.


**Starter Code:**
```text
{"prompt":"Identify the primary key and validation checks:","template":"1. Primary key field: [blank_1]\n2. BloodGroup validation check: [blank_2]\n3. NationalID length validation check: [blank_3]","word_bank":["NationalID","StudentName","Lookup check","Length check","Range check","Format check"],"blanks":[{"id":"blank_1"},{"id":"blank_2"},{"id":"blank_3"}]}
```

**Autotest Expectations:**
- **Primary key is NationalID**: `text_exact` -> Expected: `NationalID`
- **BloodGroup check is Lookup check**: `text_exact` -> Expected: `Lookup check`
- **NationalID check is Length check**: `text_exact` -> Expected: `Length check`

### Exercise 3: Independent Practice: Choose a key (Fill in blank, 30 XP)

**Instructions:**
Work out each answer before submitting. Two members can share a name. Is Name guaranteed unique: yes or no? Should a primary key allow duplicate values: yes or no? Should a primary key be missing: yes or no?


**Starter Code:**
```text
{"prompt": "Independent Practice: Choose a key", "template": "1. Two members can share a name. Is Name guaranteed unique: yes or no? [blank_1]\n2. Should a primary key allow duplicate values: yes or no? [blank_2]\n3. Should a primary key be missing: yes or no? [blank_3]", "blanks": [{"id": "blank_1"}, {"id": "blank_2"}, {"id": "blank_3"}], "word_bank": ["yes", "no"]}
```