SQL Queries and Relational Tables in LibreOffice Base

LibreOffice Base serves as a front-end interface for database management, enabling users to build relational database structures and execute SQL queries. It natively supports database design principles, such as primary and foreign key constraints, table normalization, and graphical relationship mapping, while providing both graphical and raw SQL environments for querying data. Whether using the default embedded database engine (such as Firebird or HSQLDB) or connecting to external engines via JDBC/ODBC, Base offers standard tools for robust relational data modeling and data manipulation.

Relational Table Design in LibreOffice Base

Relational table design in LibreOffice Base focuses on structuring data into distinct, non-redundant tables connected through logical keys.

1. Table Creation and Data Types

Base allows users to define tables using the Table Design View or SQL DDL (Data Definition Language). In Design View, users define: * Field Names: Column identifiers. * Field Types: Data categories including VARCHAR, INTEGER, DECIMAL, DATE, and BOOLEAN. * Field Properties: Constraints such as Entry Required (NOT NULL), default values, length, and auto-increment behavior.

2. Primary and Foreign Keys

• Primary Keys: Base requires a primary key for a table to be editable through the user interface. A primary key uniquely identifies each record and can be defined as a single auto-incrementing integer or a composite set of fields.
• Foreign Keys: Relationships between tables rely on foreign keys—fields in one table that reference the primary key of another.

3. The Relationships Tool and Referential Integrity

Base includes a graphical Relationships Window (accessible via Tools > Relationships). Users drag and drop primary keys onto corresponding foreign keys to define relationships: * One-to-One (1:1): Unique values on both sides of the link. * One-to-Many (1:N): A single record in the parent table links to multiple records in the child table. * Many-to-Many (M:N): Handled via a junction table containing two foreign keys pointing back to the parent tables.

The relationship editor enforces Referential Integrity rules, defining actions when parent records are updated or deleted: * No Action / Restrict: Prevents modifications that would orphan child records. * Cascade Delete/Update: Automatically updates or deletes associated child records when a parent record changes. * Set Null / Set Default: Replaces foreign key references with NULL or a default value when the parent record is deleted.

SQL Query Implementation in LibreOffice Base

LibreOffice Base supports data retrieval, aggregation, and manipulation through multiple querying interfaces.

1. Query Modes

Base provides three primary methods for constructing queries: * Design View (Query by Example): A graphical interface where users add tables, select fields, define sorting, establish criteria (e.g., > 100, LIKE 'A%'), and specify groupings without writing SQL syntax. * SQL View: A text editor that displays the underlying SQL statement generated by the Design View, allowing manual edits to SELECT, WHERE, JOIN, and GROUP BY clauses. * Direct SQL Mode: By default, Base parses queries through its internal parser to enable GUI features. Clicking the “Run SQL command directly” (SQL Native) button bypasses the Base parser, passing the query directly to the underlying database engine. This allows the execution of engine-specific functions, nested subqueries, and advanced syntax that Base might otherwise reject.

2. Joins and Multi-Table Queries

Base supports standard SQL joins to combine records from related tables: * INNER JOIN: Retrieves records with matching values in both tables. * LEFT / RIGHT OUTER JOIN: Retrieves all records from one table and matching records from the other. * CROSS JOIN: Generates a Cartesian product of both tables.

In SQL View, these can be written explicitly using standard ANSI SQL syntax:

SELECT 
    "Customers"."CustomerName", 
    "Orders"."OrderDate", 
    "Orders"."TotalAmount"
FROM 
    "Orders"
INNER JOIN 
    "Customers" ON "Orders"."CustomerID" = "Customers"."CustomerID"
WHERE 
    "Orders"."TotalAmount" >= 50.00
ORDER BY 
    "Orders"."OrderDate" DESC;

3. Parameter Queries

Base supports dynamic user input at runtime using parameter markers (:parameter_name or ?). When a parameter query is executed, Base prompts the user with a dialog box to enter the value, which is then inserted into the query condition:

SELECT * FROM "Products" WHERE "UnitPrice" <= :MaxPrice;

4. Direct SQL Execution for DDL and DML

While the standard Query section is intended for returning record sets via SELECT statements, administrative commands and batch updates (such as CREATE TABLE, ALTER TABLE, UPDATE, or DELETE) can be executed directly via Tools > SQL…. This bypasses UI validation and executes statements directly against the database engine.