While LibreOffice allows you to create tables using the GUI, knowing how to create tables using SQL can help you better understand how relational databases work. Once you create a database, you can access the SQL command dialog by Tools > SQL. SQL is a acronym for Structured Query Language and is the standard language for accessing and manipulating databases.
A few thing you need to know before we get started. All SQL statements in LibreOffice must end with a semicolon (;). You can type keywords in upper or lower case, but for the sake of clarity, I will put keywords in upper case, as is the SQL standard. In LibreOffice Base and HSQLDB SQL syntax, unquoted field and table names are converted to upper case. Therefore, I will always quote my fields and tables names.
Create a Table with Fields
In a previous article, I discussed the different data types used in HSQLDB. For our first table, we will create a table containing at least one field with a data type from each category.
Here is the SQL:
CREATE TABLE “test” ( “id” INT, “text” VARCHAR(50), “value” DECIMAL(50, 2), “day” DATE, “active” BOOLEAN);
So we begin the statement with CREATE TABLE <name of table>. Parentheses surround the field definitions. Our first field is a integer named “id”. The “text” field is a string of up to 50 characters. We create a DECIMAL field named “value” that can have up to 50 places with 2 of them being after the decimal. We have a DATE filed named “day”, and finally, a BOOLEAN named “active”.
Auto Generate the Identity Field
In many cases, the identity of a table is an unique integer field. This field cannot repeat between records. Each record must have a different ID. Rather than trying to track this yourself, you can have Base do it for you by creating an automatically generated integer for each record.
CREATE TABLE “test2” ( “id” INT GENERATED BY DEFAULT AS IDENTITY, “text” VARCHAR(50), “value” DECIMAL(50, 2), “day” DATE, “active” BOOLEAN);
When we define the “id” this time, we add the command GENERATED BY DEFAULT AS IDENTITY. This phrase means that the database will automatically fill in this field and use it has the identity of the table. Since this field is unique in each record, then it is the primary way of identifying each record. Making it automatic means you don't have to worry about making sure each one is unique. Each table can contain only one auto generated field. The auto generated identity field also acts as the primary index. We will talk about primary keys when we discuss constraints.
Define Default Values for Fields
There are cases where a field will need a default value, but occasionally need a different value. We can set a field to have a default value that is changed only when needed.
CREATE TABLE “test3” ( “id” INT GENERATED BY DEFAULT AS IDENTITY, “text” VARCHAR(50), “value” DECIMAL(50, 2) DEFAULT 0.00, “day” DATE DEFAULT TODAY, “active” BOOLEAN);
In table test3, we define a default for the “value” field of 0.00. We also define a default for “day” of TODAY. TODAY is a built-in value for the current date. There are many of these built in values and built-in functions, too.
Making Sure a Field Has a Value
At other times, we want a field to always have a value, but we don't want to assign a meaningless default value. For this we can tell the table a field is never blank, or NOT NULL.
CREATE TABLE “test4” ( “id” INT GENERATED BY DEFAULT AS IDENTITY, “text” VARCHAR(50) NOT NULL, “value” DECIMAL(50, 2) DEFAULT 0.00, “day” DATE DEFAULT TODAY, “active” BOOLEAN);
In table test4, we have added NOT NULL to the “text” field's definition. If a user tries to leave the field blank when inserting a record, the database will generate an error.
While this has been basic and there is more to creating tables, it gives you a good start to creating tables in LibreOffice Base using SQL. The essentials for creating a table are the ability to create an auto generated identity, set default values to fields, and tell the database that a field cannot remain blank.