Referential integrity

1.3.2 Databases

Databases — Note 5 of 6

Candidates need to understand what is meant by referential integrity, and why this is desirable in a database.

What you need to know

Referential integrity ensures that the links between tables in a relational database remain consistent. Every foreign key value must refer to a record that actually exists in the linked table.

What to know

A DBMS enforces referential integrity by preventing actions that would break a link.
Example: an order cannot be added for a customer ID that does not exist in the CUSTOMER table.
Example: a customer cannot be deleted while orders still refer to them, unless the related orders are also deleted (cascade delete).
Cascade update: if a primary key value changes, matching foreign keys are updated automatically.
Without referential integrity, a database can contain orphan records that refer to missing data, giving incorrect query results.

Key terms

Exam tipCommon mistake

Referential integrity: A rule that every foreign key value must match an existing primary key value in the related table, so no record refers to data that does not exist.
Exam tip: Explain referential integrity using the tables in the question, e.g. 'every ClassID in the Students table must exist in the Classes table'.

← Previous noteSQL: interpret and modify1.3.2 Databases Next note →Transaction processing, ACID, record locking and redundancy1.3.2 Databases

Back to Databases

Databases

Note 5 of 6

1Relational databases, flat files, keys, entity relationship modelling, normalisation and indexing
2Methods of capturing, selecting, managing and exchanging data
3Normalisation to 3NF
4SQL: interpret and modify
5Referential integrity
6Transaction processing, ACID, record locking and redundancy

Topic overview