Referential Integrity |
An Access database is a relational database that can maintain the quality of your data using referential integrity, cascading updates and cascading deletes. These terms are characteristics of the relationship between 2 tables of the database. Referential Integrity means that a value can only be entered if it already exists on another table. e.g you can't enter a Job for a non-existent Customer. If this is not used, you can easily end up with 'orphaned' records that seem to disappear. Cascading Updates is used on most relationships and means that if the key value is changed, related tables that also have that key value, will also be changed. e.g. if a Customer Id is changed, then the Id on all related Jobs will be changed as well. Cascading Deletes serves 2 purposes - one when it's implemented and one when it's NOT implemented: 1) to tidy up when records are deleted, set cascading deletes. e.g. implementing cascading deletes between Jobs and Job Lines will mean when the Job is deleted, then the Job Lines for that Job will be deleted as well. 2) to make sure records aren't deleted by mistake, do not set cascading deletes. e.g. If the relationship between Jobs and Job Invoices did NOT having cascading deletes then if someone tried to delete a Job that had been Invoiced, the deletion will be stopped. |