Referential Integrity Rules in LibreOffice Base

Maintaining data consistency is essential in database design, and LibreOffice Base provides several referential integrity rules to manage relationships between tables. This article explains the relational integrity options available in LibreOffice Base—specifically update and delete rules such as Cascade, Set Null, Set Default, and No Action—and how each rule behaves to prevent orphaned records in your database.

Accessing Relation Properties in LibreOffice Base

To configure referential integrity rules, navigate to Tools > Relationships from the main LibreOffice Base menu. When you create a relationship between a primary key in a parent table and a foreign key in a child table, double-clicking the relationship line opens the Relations dialog. This dialog provides specific options for handling both Update and Delete actions.

Relational Integrity Rules for Deleting Data

When a record in a parent table is deleted, the corresponding records in the child table are affected according to the selected delete rule:

• No Action: This is the default setting. It prevents the deletion of a record in the primary table if any matching records exist in the foreign key table. The database will return an error and reject the deletion.
• Delete Cascade (Cascade Delete): Automatically deletes all related records in the child table when the referenced record in the parent table is deleted. This prevents orphaned records but should be used cautiously to avoid unintended data loss.
• Set Null: Deletes the record from the parent table and automatically updates the matching foreign key fields in the child table to NULL. This option requires the foreign key column to allow null values.
• Set Default: Deletes the record from the parent table and resets the matching foreign key fields in the child table to a pre-defined default value.

Relational Integrity Rules for Updating Data

When a primary key value in a parent table is modified, LibreOffice Base handles the linked child records based on the selected update rule:

• No Action: Blocks any updates to the primary key in the parent table if related records exist in the child table.
• Update Cascade (Cascade Update): Automatically updates the foreign key values in all matching child records to reflect the new primary key value from the parent table.
• Set Null: Changes the primary key value in the parent table while setting the foreign key in all related child records to NULL.
• Set Default: Changes the primary key value in the parent table and reverts the linked foreign key values in the child table to their default setting.