Keys and Referential Integrity in LibreOffice Base

LibreOffice Base manages relational data by utilizing primary keys, foreign keys, and referential integrity rules to structure tables and preserve data consistency. By leveraging its integrated database engines—primarily Firebird or embedded HSQLDB—Base ensures that each record is uniquely identifiable and that relationships across different tables remain valid during data entry, updates, and deletions.

Primary Keys in LibreOffice Base

A primary key uniquely identifies each record in a database table, preventing duplicate entries and null values in the designated key column.

• Creation and Assignment: When designing a table in Design View, you designate a primary key by right-clicking the row header of the desired field and selecting Primary Key. Base also supports composite primary keys by selecting multiple fields simultaneously before assigning the key.
• Auto-Value Generation: For integer fields used as primary keys, Base allows you to set the AutoValue property to “Yes.” This enables the database engine to automatically generate a unique, sequential number for every new record.
• Table Modifiability: In LibreOffice Base, assigning a primary key is essential for data entry; tables without a primary key are treated as read-only in the graphical interface.

Foreign Keys and Relationships

Foreign keys establish links between separate tables by referencing the primary key of another table, creating parent-child relationships (such as one-to-many or one-to-one).

• Defining Relationships: Relationships are configured graphically via Tools > Relationships. In this window, you add the relevant tables and drag the primary key field from the parent table to the corresponding foreign key field in the child table.
• Data Type Matching: For a foreign key to function correctly, the foreign key column must have the exact same data type as the referenced primary key (e.g., both must be standard integers).

Referential Integrity Controls

Referential integrity prevents orphaned records by ensuring that a foreign key value always points to an existing primary key value in the parent table. When defining a relationship in the Relation Properties dialog, LibreOffice Base provides rules for handling updates and deletions:

• Update Options: Controls what happens to foreign key values when a parent primary key is modified:
  • No Action / Restrict: Blocks changes to the parent key if related records exist in the child table.
  • Update Cascade: Automatically updates the foreign key values in all related child records when the parent key changes.
  • Set Null: Sets the related foreign key values to NULL.
  • Set Default: Reverts the related foreign key values to their predefined default setting.
• Delete Options: Controls what happens to child records when the referenced parent record is deleted:
  • No Action / Restrict: Prevents deletion of the parent record as long as matching child records exist.
  • Delete Cascade: Automatically deletes all related child records whenever the corresponding parent record is removed.
  • Set Null / Set Default: Retains the child records but removes or resets their link to the deleted parent.