Advanced Database – Apply referential integrity between tables

Referential integrity means controlling how records are altered or deleted in a relational database (i.e. one with links/relationships between fields in different tables).

48 referential integrity

To define the rules of how records are altered across relationships, you must right click on the relationship line in the Relation Design window and select Edit.

This allows you to change ‘Update’ and ‘Delete’ options.

49 order ref integrity

Open the Relation Design window, and right-click on each line for the relationships made in the last example.

Set them to ‘No action’ for update and delete. No action means changing the primary key won’t affect foreign keys.

Close the window and open the ‘Orders’ table.

50 error

Try to change the Order ref for the first record to ‘450’ for instance.

You will get an error message like the one shown. This is because we have set the update option to ‘No action’.

The same would apply if we tried to delete the row, as we also set the Delete option to ‘No action’ for the relationship.

This option enforces referential integrity of the database.