How does a database keep its linked data consistent and reject bad input?
Referential integrity and entity integrity between linked tables, and validation of data entered into a database (presence, restricted choice and field length checks).
An SQA Higher Computing Science answer on database integrity and validation, covering entity integrity, referential integrity between linked tables, and validation checks such as presence, restricted choice and field length.
Reviewed by: AI editorial process; not yet individually human-reviewed
Have a quick question? Jump to the Q&A page
Jump to a section
What this key area is asking
The SQA wants you to understand how a database keeps its linked data consistent and rejects bad input: entity integrity and referential integrity between tables, and validation of data entered (presence, restricted choice and field length checks). You should be able to explain referential integrity and choose suitable validation for a field.
Why integrity matters
A database is only useful if its data is consistent and correct. Two structural rules protect this automatically: entity integrity and referential integrity. They are enforced by the database itself, so the rules cannot be broken even by a careless update.
Entity integrity
Without entity integrity you could have two records with the same key, or a record with no key at all, and the database could no longer reliably find or link a specific record.
Referential integrity
This stops two failures. An insert cannot store a foreign key for a non-existent record (you cannot record a loan to member 999 if member 999 does not exist). A delete is controlled too: deleting a member who still has loans would leave those loans as orphans pointing at a member who is gone, so the database either blocks the delete or removes the dependent records, depending on the rule set. Either way, dangling references are prevented.
Validation: checking data as it is entered
Integrity protects the links; validation protects the values. Validation checks input against rules before it is stored, rejecting anything unacceptable.
- Presence check. The field must not be left empty - used for data that is always required, such as a surname or a year group.
- Restricted choice check. The value must be one of an allowed set, such as a year group from S1 to S6, or a status of "active" or "lapsed". This blocks values outside the list.
- Field length check. The number of characters is limited to a sensible maximum (or exact length), for example a UK postcode field, preventing entries that are too long.
Examples in context
These rules are why real systems behave sensibly. An online shop will not let you delete a product that still appears on past orders (referential integrity protecting the order history), and a sign-up form rejects a blank email (presence) or an invalid country picked outside its dropdown (restricted choice). Bank databases rely on entity integrity so every account has a unique, non-empty account number. In the SQA assignment you design and apply these rules, and the question paper asks you to explain referential integrity and pick suitable validation for given fields.
Try this
Q1. State what entity integrity guarantees about a primary key. [2 marks]
- Cue. It is unique and not empty (null), so every record can be uniquely identified.
Q2. State the validation check that limits a field to an allowed set of values. [1 mark]
- Cue. A restricted choice check.
Q3. Explain one problem that referential integrity prevents. [1 mark]
- Cue. It prevents orphan records, such as a loan referring to a member who does not exist or who has been deleted.
Exam-style practice questions
Practice questions written in the style of SQA exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.
SQA Higher (style)4 marksA Loan table has a memberID foreign key referring to the Member table. Explain what referential integrity means here, and describe one problem that would occur if it were not enforced.Show worked answer →
Referential integrity means that every foreign-key value in the Loan table must match an existing primary-key value in the Member table (or be empty if allowed). In other words, a loan can only refer to a member who actually exists.
If referential integrity were not enforced, a loan could store a memberID that does not exist in the Member table, creating an "orphan" record. For example, deleting a member while they still have loans would leave those loans pointing at a member who is gone, so a query joining loans to members would lose or misreport that data.
Markers reward defining referential integrity as foreign-key values matching an existing primary key, and a valid problem such as orphan records from inserting an invalid memberID or deleting a referenced member.
SQA Higher (style)3 marksA database field stores a pupil's year group, which must be one of S1 to S6 and must always be entered. State two validation checks suitable for this field and what each prevents.Show worked answer →
Two suitable checks:
- A presence check: the field must not be left empty. This prevents a record being stored with no year group.
- A restricted choice check: the value must be one of the allowed set S1, S2, S3, S4, S5, S6. This prevents an invalid year group such as "S7" or "Year 9" being entered.
(A field length or format check could also limit the entry to two characters.)
Markers reward two appropriate checks (presence and restricted choice are the natural pair here) and a correct statement of what each prevents.
Related dot points
- Analysing a database problem and designing the data model: entity-relationship diagrams, entities and attributes, relationships and cardinality, the data dictionary, and primary, foreign and compound keys.
An SQA Higher Computing Science answer on database analysis and design, covering entity-relationship diagrams, entities and attributes, relationships and cardinality, the data dictionary, and primary, foreign and compound keys.
- Querying a relational database with SQL SELECT, including WHERE, joining multiple tables, ORDER BY, aliases, wildcards, computed values, aggregate functions and GROUP BY.
An SQA Higher Computing Science answer on querying a relational database with SQL, covering SELECT with WHERE, joining multiple tables, ORDER BY, aliases, wildcards, computed values, aggregate functions and GROUP BY.
- Changing the data in a relational database with SQL INSERT, UPDATE and DELETE on prepopulated linked tables, and the implications of these operations.
An SQA Higher Computing Science answer on changing data in a relational database, covering the SQL INSERT, UPDATE and DELETE operations on prepopulated linked tables and the implications of each.
- Testing with normal, extreme and exceptional test data; syntax, execution and logic errors; debugging techniques; and evaluating software for fitness for purpose, efficiency, robustness and readability.
An SQA Higher Computing Science answer on testing and evaluation, covering normal, extreme and exceptional test data, the three error types, debugging techniques, and evaluating software for fitness for purpose, efficiency, robustness and readability.