Objective: Know and understand the characteristics of primary keys and foreign keys.
What is a Primary Key?
A primary key is a field (or combination of fields) that uniquely identifies each record in a table.
Uniqueness – no two rows can have the same primary key value.
Non‑null – every record must contain a value for the primary key.
Immutability – the value should not change frequently.
Single‑field or composite (multiple fields).
What is a Foreign Key?
A foreign key is a field (or combination of fields) in one table that refers to the primary key of another table, establishing a relationship between the two tables.
Referential integrity – the foreign‑key value must match an existing primary‑key value or be null (if allowed).
Enforces logical links such as one‑to‑many, many‑to‑many (via junction tables).
Can be used to cascade updates or deletions.
Key Characteristics Comparison
Aspect
Primary Key
Foreign Key
Purpose
Uniquely identifies a record within its own table.
Creates a link to a record in another table.
Uniqueness
Must be unique.
Not required to be unique.
Null values
Cannot be null.
May be null (if relationship is optional).
Location
Defined in the table it identifies.
Defined in the child (referencing) table.
Number per table
One per table (though composite keys count as one).
Zero, one, or many per table.
Enforces
Entity integrity.
Referential integrity.
Example Scenario
Consider a school database with two tables: Students and Enrollments.
In this design, StudentID in Enrollments must match a value that exists in Students.StudentID, ensuring that every enrollment record is linked to a valid student.
Rules for Defining Keys
Choose a field that is stable and never changes (e.g., an automatically generated ID).
Avoid using personal data (e.g., National Insurance Number) as a primary key unless required.
If no single field is unique, create a composite primary key using multiple fields.
When creating a foreign key, ensure the referenced primary key is defined before the foreign key constraint.
Consider cascade actions:
ON UPDATE CASCADE – changes in the primary key propagate to foreign keys.
ON DELETE CASCADE – deleting a parent record also deletes related child records.
Common Mistakes to Avoid
Using a non‑unique field as a primary key.
Allowing null values in a primary key column.
Creating a foreign key that references a non‑primary (or non‑unique) column.
Neglecting to enforce referential integrity, leading to orphan records.
Summary
Understanding primary and foreign keys is fundamental for designing relational databases that are accurate, efficient, and maintain data integrity. The primary key uniquely defines each record, while the foreign key creates meaningful relationships between tables, enforcing referential integrity.
Suggested diagram: Entity‑Relationship diagram showing the Students table (primary key StudentID) linked to the Enrollments table via the foreign key StudentID.