Primary Keys & Foreign Keys
What is a Primary Key?
- Each table has a primary key field which acts as a unique identifier
- Each item of data in this field is unique
- Duplicate data items would be blocked if they were entered into the primary key field
- Because the items of data are unique within the primary key field they can be used to identify individual records
A Database Table Containing Customer Details
|
|
|
|
|
|
|
1 |
Andrea |
Bycroft |
05031976 |
0746762883 |
|
2 |
Melissa |
Langler |
22012001 |
0756372892 |
|
3 |
Amy |
George |
22111988 |
0746372821 |
- In the example customer table, the primary key would be the CustomerID because each customer’s ID is unique
- If there was a customer with the same name they could be identified correctly using the CustomerID
Creating and Editing Keys
- Primary key - Uniquely identifies each record in a table
- Foreign key - Used to link two tables together. The Foreign Key in one table would be the primary key in another
Creating Relationships Between Tables
- Relational databases allow you to create relationships between different tables using primary and foreign keys
What is a Foreign Key?
- A foreign key is how we link tables together using primary keys
- Using the table above with customer details, we'll add another table showing subscriptions that customers have
- In this example CustomerID is a foreign key as it links with the CustomerID in the customer table above
SubscriptionID |
CustomerID |
SubscriptionType |
| 1 | 2 | Annual |
| 2 | 1 | Monthly |
| 3 | 3 | Quarterly |
Exam Tip
- If the answer to a question is the name of a field, ensure you copy it exactly from the question. The examiner is looking for an exact answer with the correct capital letters and underscores where they're included
Worked example
A systems analyst has created a new computer system to keep records in a medical centre. She has created a relational database to store the medical records of patients.
The database uses primary and foreign keys. Explain the difference between a primary key and a foreign key.
[4]
4 of:
The primary key holds unique data [1]
The primary key identifies the record [1]
The primary key can be automatically indexed [1]
Each table has one primary key whereas a table can contain several foreign keys [1]
A foreign key is used to link with the primary key of another table [1]
