Referential Integrity provides you with the ability to set rules to ensure that linked records are not deleted when they are being relied upon by other records, or to ensure that a deletion will 'tidy up' and delete other records that are related to it and no-longer needed.

By default, referential integrity is not enforced. A record can be deleted in a table regardless of whether it is linked to by a record in another table. The record and link are removed, but the other record is left alone.

Record Link 

Referential Integrity is an attribute of a special, and very commonly used, field-type - Record Link. 

The Record Link allows you to link one table field to another Table or to a Query

Referential integrity provides two additional options:

Delete the record in the linked table.

With this option selected, both the record that was asked to be deleted, and the record in the linked table are deleted.

However, if another integrity constraint prevents the linked record from being deleted, then neither record is deleted.

Stop the record in this table being deleted.

With this option selected, the record cannot be deleted if there is a link to another record. The user must first either remove the link value from the record so it is no longer pointing at a record, or directly request the other record is deleted first.

The Referential Integrity dialog provides two sets of these options

• the first one determines the constraints when the record in this table is requested to be deleted, and
• the second works in the other direction, determining the constraints when the record in the linked table is requested to be deleted.

Referential Integrity rules can cascade

The deletion of a record in table A may trigger the deletion of a linked record in table B, and that might trigger the deletion of a record in table C

The deletion of a record in table A may request the linked record in table B being deleted, but a constraint on the link between table B and C may prevent the record in table B being deleted. And because the record in table B cannot be deleted, the request to delete the record in table A is refused.

Cascades can 'fold back' on the original table.

For example, table A could have a link to table B, with two constraints specified: when an attempt is made to delete the record in table A, delete the record in table B, and when and attempt is made to delete the record in table B, stop the deletion.

Then if there are two records in table A, both linked to the same record in table B, a request to delete one of the records in table A will be refused, as the constraint from the other record in table A will stop it.

However if there was only one record in table A linked to the record in table B, the constraint would not stop it being deleted as circular constraints on the same record are not enforced.

Table Component - Maintenance tab - Deleting all records

The Table component provides a tool to delete all records in the table. When executed this will provide a choice as to whether it should respect the referential integrity rules or ignore them. (You may wish to ignore them if you are deleting all the records from several linked  tables in preparation for cloning the site, and you know that when you are done there will be no records remaining in violation of the constraints anyway)

Bulk import

Some bulk import scripts involve deleting records. Such deletions will be subject to the referential integrity rules. The script will continue to execute even if some of its requested deletions were refused.

Usage examples

A blog site containing Blog postings in one table, and Comments about the blog postings in another.

If the blog posting is deleted, the comments should be deleted too, as they are meaningless without the context of the blog posting.

An ecommerce site with products and components that make up the products.

If the component is used in a product, prevent the component being deleted. (The user would first have to manually decide to delete that product first, or change the product to use an alternative component, before re-attempting to delete the component)