LibreOffice Base SQL: Creating Tables

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.

About these ads

5 comments on “LibreOffice Base SQL: Creating Tables

  1. […] far, we have looked at the different data types and creating tables using SQL in LibreOffice Base with the default HSQLDB database management system. Sometimes when you create a […]

  2. Taimur says:

    Thanks for this tutorial, I have one issue that any table created using SQL does not appear in tables until the time I close base and restart it.

    I am using Libre Office on Linux (Manjaro), please help if you can. Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s