LibreOffice Base SQL: Altering Tables

So 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 table using SQL, you don’t always get it right the first time, so SQL allows you to alter, or change, the structure and layout of the table.

As a reminder, to get to the SQL entry dialog in Base use the menus, Tools > SQL.

Starting Table

In order to talk about altering tables, we need a table to start with. The following SQL creates the table we will alter.

CREATE TABLE "users" (
    "ID" INT GENERATED BY DEFAULT AS IDENTITY,
    "Name" VARCHAR(40),
    "Password" VARCHAR(20),
    "Current" BOOLEAN);

Insert a New Field into the Table

Our table has a field named “Name”, but suppose we decide to separate first and last name. We would need to insert a new field into our table. We will want the two name fields to stay next to each other in the table. We will insert the new field “FirstName” before “Name” with the following SQL statement:

ALTER TABLE “users”
    ADD “FirstName” VARCHAR(20)
    BEFORE “Name”;

Rename a Field

Since we have created the field “FirstName”, we will want to change “Name” to “LastName”. This is easily done using an ALTER COLUMN statement.

ALTER TABLE “users”
    ALTER COLUMN “Name”
    RENAME TO “LastName”;

Remove a Field from Table

We were going to use the “Current” field to determine whether a user was active, but after thinking about it decided we would just delete inactive users. This means we don’t need the “Current” field in the table anymore. We use the DROP statement to remove the field.

ALTER TABLE “users” DROP “Current”;

Set a Default Value for a Field

After much consideration, we decide that we need to assign a default value to the password field in case the user doesn’t fill it in when creating their account. Once again we use the ALTER COLUMN statement to accomplish the job.

ALTER TABLE “users”
    ALTER COLUMN “Password”
    SET DEFAULT 'password';

Note the use of single quotes for the default string rather than double quotes.

Restart Count on Identity Field

We now have our table the way we want it. We do some testing, then we delete all the test data. We want to reset the auto-generated identity field to start at 1 before putting the database into production. Yet another ALTER COLUMN statement accomplishes the job.

ALTER TABLE “users”
    ALTER COLUMN “ID”
    RESTART WITH 1;

You could use any restart number you wish, 4, 8, 15, 16, 23, 42, etc.

That is the basics of altering tables. Next we will look at Table Constraints.