databasedev.co.uk - database solutions and downloads for microsoft access

Data Integrity

Microsoft Access recognizes parent-child type relationships and embeds methods to ensure that the integrity of these relationships remains valid during your data processing operations. Microsoft Access institutes a system of rules to establish integrity and enforce integrity, once it is established. Integrity can be established for one-to-one or one-to-many relationships during the creation of the relationship or it can be established when modifying existing tables to establish a relationship. The rules of integrity, by default, are designed to prevent the incidence of orphaned table records on the “many” side of relationships. As such, Microsoft Access prohibits the deletion of parent records or the modification of primary key values when associated child records exist. If referential integrity is not established, an indeterminate relationship exists between tables.

Microsoft Access also provides methods to override this default behaviour for established relationships. The Microsoft Access Relationships toolbar provides two check boxes, Cascade Update Related Fields and Cascade Delete Related Records, as options to override the default behaviour. Both options are applicable to tables where referential integrity has already been established.

The Cascade Update Related Fields option allows you to change the primary key value (parent) of the primary table and have that change cascade down to the foreign key value (child) of the foreign key table. This option is only applicable to primary keys that consist of a number or text value. The Cascade Update Related Fields option has no effect when the primary key is a unique index, generated by the Microsoft Access auto-number feature. Auto-number values cannot be changed. You must be careful to ensure that a foreign key value is defined to correspond with the primary key value that is being changed. If Microsoft Access does not find a corresponding foreign key value in the corresponding child table, Microsoft Access will provide no indication that the cascade has failed. It will only update the primary key value.

The Cascade Delete Related Records option allows you to delete a primary record (parent) from the primary table even if related, foreign records (child) exist in the foreign table. Microsoft Access will automatically delete the related foreign records to maintain integrity of the tables. If you attempt to delete a primary record and there are no related foreign records, Microsoft Access produces a warning message that gives no indication that the cascade has failed. The warning will allow you the option of continuing with the deletion of just the primary record or aborting the process. When Microsoft Access is able to detect the presence of related foreign records, you will be issued a message indicating Microsoft Access’s intent to delete records from both the primary and foreign tables. You should carefully consider all options before implementing this override. Deleting records from either primary or foreign tables may have detrimental effects on other fields, in your database, that rely on or make calculations based on data contained in these records. You may find it more appropriate to archive the data or modify data in the record fields to reflect unavailability or null values.