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 | StudentName | TutorGroup | JoinDate |
|---|---|---|---|
| S001 | Maya Chen | A | 2026-09-01 |
| S002 | Leo Patel | A | 2026-09-01 |
| S003 | Maya Chen | B | 2026-09-03 |
| S004 | Aisha Khan | B | 2026-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.
| Item | Meaning |
|---|---|
| StudentID | The primary-key field |
| S003 | One key value |
| The S003 row | The record identified by that key |
| Maya Chen | A 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.
| Candidate field | Concern | Suitable here? |
|---|---|---|
| StudentName | Two students already share a name | No |
| TutorGroup | Many students share a group | No |
| JoinDate | Several can join on one date | No |
| StudentID | Assigned unique and required | Yes |
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.