Understanding the Primary Key (“PK”) concept is critical to learning about Relational Databases. A table can exist without a PK, but in a Relational Database, you need the PK(s) to create relationships between your tables.
In this post:
Required knowledge:
1. Definition
The Primary Key (“PK”) of a table is the field that contains the value which uniquely identifies each record in that table.
The PK of a record:
- Is required (cannot be left blank)
- Must be unique
Because relationships between tables involve the Primary Key, the PK value should never change.
2. Candidates
In a table created to store the details of the members of a club, we might see the following data:
| mSurname | mFirstName | mSAID | mDOB | mCellNumber |
|---|---|---|---|---|
| Blanks | Billy | 7008255024684 | 1970/08/25 | |
| Smith | Jacqueline | 8805063644549 | 1988/05/06 | 0826455597 |
| Fox | Mister | 9109115072084 | 1991/09/11 | 0725688491 |
Based on the definition of the PK, the fields mSurname, mFirstName, mSAID and mDOB all qualify as candidate PKs as the values of each record in those fields are unique and not blank. mCellNumber does not qualify as the field in the first record is blank (even if there were no blanks, it would not be a good idea as it is quite possible that a person could change cellphone numbers at a future date).
We can also quite easily predict that in the future we will have to record more than one person in the table with the same surname, making mSurname a poor choice of PK. The same would go for mFirstName with members with the same first name and mDOB with members sharing a birthday.
This leaves us with mSAID. There are a few potential problems with using this field: what happens if the member does not have their South African ID document, or if a non-South African person wants to join the club? Additionally, using such a large number stored as text will create problems.
3. Real-world
In the real world, PKs usually contain some simple sequential number: essentially 1, 2, 3 etc.
When inserting a new record in such a table, this requires that you first establish what the highest number in the PK field is, then increment that number by 1 and use that new number as the PK of the record being inserted.
| mID | mSurname | mFirstName | mSAID | mDOB | mCellNumber |
|---|---|---|---|---|---|
| 1 | Blanks | Billy | 7008255024684 | 1970/08/25 | |
| 2 | Smith | Jacqueline | 8805063644549 | 1988/05/06 | 0826455597 |
| 3 | Fox | Mister | 9109115072084 | 1991/09/11 | 0725688491 |
4. (Auto)number
It follows then that the PK is set to a data type that automatically ensures a sequential number is inserted for each new record; in Microsoft Access the AutoNumber data type, in MySQL the AUTO_INCREMENT feature. [2]
A table can exist without a Primary Key, although there almost always is one. This is partly because the PK is indexed: the index of a table is an important feature that enables the database application to perform operations such as searches and sorts. Because it is used as the index (or one of the table’s indexes) it is best that the PK is a number and that it is as short as possible (a field can be indexed without being the PK).
5. Compound & Composite keys
A PK is usually one field but can be multiple fields. This is not dealt with in the DBE CAPS CAT syllabus but is part of the Cambridge International AS Level and the IEB IT syllabi.
Compound keys and composite keys are both used to create a unique value. A composite key is a candidate key consisting of two or more attributes (fields) that together uniquely identify a record. A compound key is a type of composite key with a more specific meaning.
A composite key is a candidate key consisting of two or more attributes (table columns) that together uniquely identify an entity occurrence (table row).
So, the difference between a composite key and a compound key is that any part of the compound key can be a foreign key, but the composite key may or may not be part of the foreign key.
A compound key is created when two or more primary keys from different tables are present as foreign keys within an entity. The foreign keys are used together to identify each record uniquely. This is how we create linking tables to form a relationship between two tables that are in an implied many-to-many relationship.
References:
- Composite key – Wikipedia. [online]. Available at: https://en.wikipedia.org/wiki/Composite_key. (Date Accessed: 24 August 2023)
- SQL AUTO INCREMENT Field (no date) SQL AUTO INCREMENT a Field. Available at: https://www.w3schools.com/sql/sql_autoincrement.asp (Accessed: 26 December 2023).