CIE IGCSE | 9.3 Primary Keys

Lesson objective

Explain the purpose of a primary key, identify suitable fields from database requirements, and distinguish uniqueness from format, truth and row position.

Learn

9.3 | PRIMARY KEYS

01 | WHICH MAYA CHEN DO YOU MEAN?

A school club has two students named Maya Chen. One is in tutor group A and one in group B. An instruction to update “Maya Chen’s record” is ambiguous: which student should change?

A name is useful information, but it is not guaranteed to identify exactly one person. A primary key is the selected field, or set of fields, whose values uniquely identify each record in a table.

For this single-table lesson, we focus on choosing one field as the key. A StudentID can distinguish the two records even when their names match.

02 | SEE THE REPEATED VALUES

The Students table contains four records. One record describes one student’s membership of this club. Names, tutor groups and joining dates can repeat.

StudentID values are assigned by the system and differ for every student record. S003 identifies one particular Maya Chen; S001 identifies the other.

StudentID is the selected primary-key field
StudentIDStudentNameTutorGroupJoinDate
S001Maya ChenA2026-09-01
S002Leo PatelA2026-09-01
S003Maya ChenB2026-09-03
S004Aisha KhanB2026-09-03

03 | UNIQUE MEANS NO DUPLICATE KEY VALUES

A unique key value appears in only one record of the table. No two student records may have the same StudentID while StudentID is the primary key.

If another record also used S003, selecting S003 would no longer identify exactly one record. A DBMS with a primary-key constraint rejects an insertion or update that would create this duplicate.

Other fields can repeat. Two students may share a name or group without breaking the StudentID primary key. The restriction applies to the selected key, not to every value in the table.

04 | THE KEY MUST BE PRESENT

A primary key cannot be NULL: every record needs its identifying key value. NULL means an absent or unknown database value, not the number zero or the literal word “NULL”.

Without a key, the record would lack the identifier promised by the table’s design. A suitable DBMS constraint prevents storing a NULL primary key.

For a text identifier such as StudentID, also require a meaningful non-empty code. An empty STRING and NULL are technically different in many systems, so a non-NULL constraint alone should not be treated as a universal check against empty or whitespace text. This school’s rule rejects those unusable identifiers too.

05 | FIELD NAME AND KEY VALUE HAVE DIFFERENT ROLES

StudentID is the primary-key field name. S003 is one primary-key value within that field. The complete S003 record also contains Maya Chen, B and its joining date.

A key is not the whole record’s content. It is the identifier used to distinguish that record. Looking up S003 retrieves the intended row even though StudentName is shared.

Do not say “the third row is the primary key”. Row order can change. The stored key value stays associated with its record when the table is sorted.

Field, identifier value and record are distinct
ItemMeaning
StudentIDThe primary-key field
S003One key value
The S003 rowThe record identified by that key
Maya ChenA non-key field value that may repeat

06 | TEST A FIELD AGAINST THE FULL REQUIREMENTS

Start by asking whether every record will have a value and whether duplicates can arise, including future records. A small sample showing different values is evidence about that sample, not proof of a permanent rule.

JoinDate may happen to differ in another table sample, but several students can join on the same date. Phone numbers may be shared, missing or changed. A full name can repeat.

A school-issued StudentID with guaranteed uniqueness and required values is a stronger choice. State the guarantee as part of the design rather than hoping it happens.

Choose from requirements, not appearance alone
Candidate fieldConcernSuitable here?
StudentNameTwo students already share a nameNo
TutorGroupMany students share a groupNo
JoinDateSeveral can join on one dateNo
StudentIDAssigned unique and requiredYes

07 | AN EXISTING UNIQUE IDENTIFIER CAN WORK

A table may already store a suitable code assigned by an organisation. If the code is guaranteed unique for the records represented and always available, it can serve as the primary key.

In a one-record-per-student table, a school StudentID may be appropriate. In a table with one record per attendance event, StudentID alone would repeat for the same student on different dates, so it would not uniquely identify those event records.

The suitability depends on what a record represents. Never choose a key before identifying the table’s record meaning.

08 | CREATE AN ID WHEN NO EXISTING FIELD IS SUITABLE

If all meaningful business fields can repeat or be missing, the system can assign a separate RecordID. This identifier exists specifically to distinguish records rather than describe their attributes.

For example, BookingID can identify one booking even if the same student makes several bookings. The generated IDs must be assigned without duplicates.

Some DBMSs provide automatic numbering, but selecting a field named ID does not by itself create or enforce uniqueness. The field must actually be designated as the primary key, and its values must meet the constraint.

09 | THE DATA TYPE DOES NOT HAVE TO BE INTEGER

A primary key may use an appropriate type such as INTEGER or text/alphanumeric. S003 is a text code; the INTEGER identifier 103 could also be suitable under an appropriate unique-assignment rule.

The key’s job is identification. It need not represent a count or an amount used in calculations. Preserve leading zeros when they belong to the required identifier format.

A format check and a uniqueness constraint do different jobs. S003 has the correct S-plus-three-digits pattern, but it cannot be added again if already used.

10 | TRY A PROPOSED KEY VALUE

The example table already contains S001, S002, S003 and S004. Check a proposed StudentID against the school’s format, required-value rule and uniqueness requirement.

    Try S003 for a duplicate, S005 for a new code, an empty value, and X005 for a format error. This checks a proposal against a fixed example table; it does not insert a record into a live database.

    An accepted key proposal does not prove the student’s other data is accurate. Those fields need their own checks and collection process.

    11 | UPDATE THE RIGHT RECORD

    A stored identifier gives an update a precise target. To correct the tutor group of S003, select that key value rather than all records whose StudentName is Maya Chen.

    The non-key data can change while the record remains the same identified item. A changed name does not imply a new student record if the student identifier is unchanged.

    The next SQL lesson will show how queries select data. For now, explain the purpose: a unique key lets an operation refer to one intended record rather than an ambiguous group of matches.

    12 | STABLE AND SIMPLE ARE USEFUL DESIGN QUALITIES

    Uniqueness and a non-NULL value are the defining primary-key constraints. Stability is a helpful design quality: an identifier that changes frequently creates unnecessary work and can confuse users or dependent processes.

    Keep the key simple enough to use reliably. A long descriptive label combining changeable details may be harder to manage than a clear assigned code.

    A primary key is not required to remain unchangeable forever, but choosing a stable identifier reduces avoidable changes. If a key is updated, the new value must still be unique and non-NULL.

    13 | A KEY IS NOT A PASSWORD OR A ROW NUMBER

    S003 may be visible in a table or form. Its role is to identify a record, not prove that a user has permission to see or edit it. Access control is a separate feature.

    The third visible row may move when data is sorted or another record is inserted. An un-stored display row number is not a dependable record identity. A stored ID allocated independently of display order can be.

    A primary key does not imply the table is displayed in key order, nor does it guarantee the other field values are correct or that the file is backed up.

    14 | COMBINED KEYS: RECOGNISE THE IDEA

    A primary key can be a set of fields when their combination uniquely identifies a record. This is often called a composite key.

    For a table whose requirement allows exactly one attendance record per student per date, the combination StudentID and AttendanceDate could be unique even though either field alone repeats.

    This is an extension to help you interpret the definition. The main focus here is identifying a suitable single field from the given single-table requirements. A table has one selected primary key, which may contain one field or a combination; it does not have a separate primary key for each row.

    15 | JUSTIFY THE CHOICE IN CONTEXT

    A useful explanation is: “StudentID is suitable because every student record receives a different, required identifier, therefore its value identifies exactly one record even when names repeat.”

    For an unsuitable field: “StudentName is unsuitable because different students can share a name, resulting in more than one matching record.” That links the problem to uniqueness rather than merely saying the name is bad.

    Check what one record represents, whether values are always present, whether the field is guaranteed unique and whether a stable, manageable identifier can be chosen.

    The next lesson uses SQL to retrieve and process data from a single table.

    MATCH THE KEY IDEA TO ITS ROLE

    Terminology

    Terminology

    Primary key

    The selected field or set of fields whose values uniquely identify each record in a table.

    Unique

    A key value is not shared by another record in that table.

    Primary-key field

    The field designated as the identifying key.

    Primary-key value

    The identifier stored for one record in the key field.

    Record

    The collection of field values describing one item.

    Duplicate key

    A repeated key value that would fail the uniqueness requirement.

    NULL

    An absent or unknown database value, not zero or the literal word NULL.

    Non-NULL requirement

    The primary key must have a value for every record.

    Constraint

    A database rule restricting which stored values are permitted.

    Non-key field

    A field not part of the selected primary key; its values may repeat unless another rule applies.

    Record meaning

    What one row represents, such as one student or one booking.

    Assigned identifier

    A code allocated to distinguish records.

    Generated ID

    An identifier created by the system under an allocation rule.

    Stable key

    An identifier chosen to avoid unnecessary changes.

    Format check

    A test of the identifier’s character pattern, distinct from uniqueness.

    Composite key

    A primary key formed from a combination of fields.

    Access control

    Rules about who may use data, separate from record identity.

    DBMS

    Software managing data and enforcing configured database constraints.

    Questions

    Questions

    CHECK YOUR UNDERSTANDING

    Select all correct choices. Each exact set earns one point.

    1. Which are primary-key requirements?
    2. Which apply to the Students example?
    3. Which proposals break the example primary-key constraint?
    4. Why is JoinDate unsuitable here?
    5. Which distinguish format and uniqueness?
    6. Which are true about key values and row order?
    7. Which are useful design qualities?
    8. Which claims are correct?

    EXPLAIN AND COMPARE

    Write your answers before revealing each sample.

    1. Define a primary key and state its main requirements.

    2. Explain why StudentName is unsuitable for the example table.

    3. Identify the key field and a key value in the Students example.

    4. Explain why an unused-looking field in a small sample may still be unsuitable.

    5. Compare a format rule with the primary-key uniqueness requirement.

    6. Explain why changing display order does not change record identity.

    7. Suggest a key for a table containing several bookings per student.

    8. Evaluate a stable assigned ID compared with a person’s name.

    CHOOSE IT | EQUIPMENT LOANS

    A table has one row per equipment loan. The same student can borrow several items, an item can be loaned again later, and several loans can begin on the same date. Fields are LoanID, StudentID, EquipmentID and StartDate. LoanID is assigned uniquely and is required. Identify a suitable primary key and explain why each other field alone is unsuitable.

    Reveal one possible answer

    LoanID is suitable because every loan record has a different required identifier. StudentID repeats when the same student makes several loans. EquipmentID repeats when an item is borrowed again. StartDate repeats when multiple loans begin on one date. The decision follows the meaning of a record: one loan, rather than one student or one item.

    Test the key rule with a duplicate LoanID and a NULL LoanID; both should be rejected. An unused, correctly assigned LoanID can pass the identifying constraint but still needs the rest of its record checked appropriately.

    Flashcards

    Flashcards

    Click to flip. Select the ideas you need to revisit.

    0 cards selected for revision.

      Selections are kept while this page is open.

      Workbook

      Workbook

      COMING SOON

      The workbook for 9.3 Primary Keys is coming soon. Complete the matching task and written questions in your exercise book.