Keeping Database Links Correct
Keeping Database Links Correct
Open from students' prior knowledge of tables, records and fields. The lesson thread is that related tables are only useful if their links stay valid over time.
Learning goal
I can explain and apply referential integrity in a relational database, and show how LibreOffice Base maintains it.
Deze slide heeft geen instructies
What words come to mind when table links go wrong?
Use this to surface prior ideas such as missing data, wrong links, errors, duplicates, broken records or reports. Connect their words to the lesson focus on keeping links valid.
Primary keys and foreign keys
A primary key uniquely identifies each record in a table
A foreign key stores a value that links to the primary key in another table
Together, they connect related tables in a relational database
Example: `StudentID` in Students links to `StudentID` in Results
Keep the example simple and consistent across later slides, such as Students and Results or Customers and Orders.
How relationships work
One record in a main table can be linked to many records in another table
The foreign key repeats the matching primary key value
The link is based on matching values, not on record position
Stress that relationships depend on field values and field types matching correctly. Students often think tables are linked visually rather than by stored key values.
What referential integrity means
Referential integrity means every foreign key value must refer to a valid existing primary key value in the related table. This rule prevents broken links between records.
This is the core definition for the lesson. Keep returning to 'valid existing primary key value' as the test for whether a link is correct.
Why this matters
Keeps data consistent across tables
Improves accuracy by stopping invalid links
Makes queries and reports more reliable
Reduces duplication and confusion in large databases
Link this to practical outcomes: if links are wrong, reports can show missing, duplicated or misleading information.
Enforcing valid links between tables
A foreign key cannot contain a value that does not exist as a primary key in the related table
The linked fields should use compatible data types and meanings
Relationship rules are used to stop invalid records being saved
You can point out that correct field design matters before integrity rules can work properly: matching field type, size and purpose.
What does a foreign key do in a relational database?
Stores repeated data
Links to another table
Sorts the records
Names the database
Checks the core idea from slides 2 to 4. Distractors reflect common confusion between keys, data storage and table features.
Complete the database summary
A primary key uniquely identifies each record in a table. A foreign key stores a value that links to another table. Referential integrity means each foreign key matches a valid existing primary key value.
Echoes the exact lesson language so students secure the load-bearing terms before moving on.
Consistency and accuracy
Consistency means related tables agree with each other
Accuracy means each link points to the correct record
Referential integrity protects both at the same time
Distinguish the two terms clearly: consistent data can still be wrong if entered incorrectly, but referential integrity specifically guards the correctness of links.
Insert operations
When adding a record with a foreign key, the matching primary key must already exist
If the related main record is missing, the new linked record should not be accepted
This prevents invalid links from entering the database
Example: a Results record for StudentID 205 cannot be added unless StudentID 205 already exists in Students.
Update operations
Changing a primary key can affect every related foreign key
If key values stop matching, links become invalid
Integrity rules help prevent or control changes that would break relationships
Students may not expect primary keys to change, so explain that updates are risky because other tables depend on those values.
Delete operations
Deleting a record from the main table can leave related records without a valid link
These broken records are a major integrity problem
Database rules can block the deletion or handle linked records in a controlled way
Use an example such as deleting a student while their results records still exist.
What can go wrong
Insert with missing parent key → invalid linked record
Update key without matching changes → mismatched keys
Delete main record without control → orphan records
This slide summarises the cause-and-effect pattern across insert, update and delete. Read each line as a practical database risk.
Orphan records
An orphan record is a record with a foreign key value that no longer has a matching primary key in the related table. It still exists, but its link is broken.
This is one of the most important error types for examination language. Make sure students can define it precisely.
Mismatched keys
A foreign key value does not match any primary key in the related table
This may happen through incorrect entry or unsafe updates
Queries, forms and reports may then show incomplete or misleading data
Differentiate mismatched keys from orphan records: both are invalid links, but orphan records often arise after deletion, while mismatches can also come from entry or update errors.
Spotting integrity problems
Missing related records in forms or reports
Key values that should match but do not
Records in a linked table that have no valid parent record
Errors when trying to save inconsistent data
Keep this practical: students should recognise that integrity problems show up in outputs, not only in table design.
Which action can create an orphan record?
Deleting a main record
Adding a valid record
Sorting a linked table
Renaming a field label
Targets the cause-and-effect summary in slides 8 to 12. Students often confuse general edits with integrity-breaking operations.
Complete the operations summary
During an insert, the matching parent key must already exist. During an update, changing a key can break existing links. During a delete, related records may become orphan records if the main record is removed.
Reinforces the different effects of insert, update and delete using the same vocabulary as the content slides.
LibreOffice Base and relationships
LibreOffice Base can define relationships between tables
These relationships connect a primary key to a foreign key
Once set correctly, Base helps maintain valid links between related records
Keep the focus on Base as the practical tool named in the syllabus, not on relational theory alone.
Defining table relationships
Choose the related tables
Select the matching key fields
Create the relationship between primary key and foreign key
You can mention the Relationships view in LibreOffice Base, but avoid overloading the slide with interface detail.
Maintaining integrity in Base
Base uses relationship settings to protect linked data
Invalid foreign key entries can be prevented
Changes and deletions in related tables can be controlled to avoid broken links
Tie this back to insert, update and delete operations from earlier slides so students see the software enforcing the database rule.
Applying the idea in practice
When a database is designed in LibreOffice Base, referential integrity means linked tables stay trustworthy over time. Correct relationships stop invalid entries, reduce errors and keep reports dependable.
This lands the lesson: referential integrity is not just a definition, but a design rule with practical effects on real database use.
How does Base help keep links valid between tables?
Expected elements: LibreOffice Base lets users choose related tables, match the primary key to the foreign key, and use relationship settings to stop invalid entries or control changes and deletions.
In LibreOffice Base, what is matched to create a relationship?
Two record colours
Two table names
Matching key fields
Matching report titles
Checks practical understanding from slides 15 to 18: relationships are created by matching a primary key and a foreign key.
What is one idea from this lesson you will remember?
Discussion prompt only. Encourage students to mention one clear idea such as valid links, orphan records, insert/update/delete effects, or how Base enforces relationships.
What helped you understand referential integrity best?
Discussion prompt only. Look for comments about examples, the Students and Results model, the insert-update-delete sequence, or the Base relationship steps.
Glossary
relational database - a database that stores data in related tables
primary key - a field that uniquely identifies each record in a table
foreign key - a field in one table that links to a primary key in another table
relationship - a defined link between tables using matching key fields
referential integrity - the rule that every foreign key must match a valid primary key
consistency - the state where related data agrees across tables
accuracy - the state where data and links are correct
Deze slide heeft geen instructies
Glossary
insert - adding a new record to a table
update - changing data in an existing record
delete - removing a record from a table
orphan record - a record whose foreign key has no matching primary key
mismatched keys - key values that should match across tables but do not
LibreOffice Base - a database application used to create tables and relationships
Deze slide heeft geen instructies