Create a database with Database Wizard in LibreOffice Base

This page demonstrates how to set up an embedded database in LibreOffice’s front-end database editor, Base. It also shows how to use the Table Wizard to create the first table in your database.

So this page will take you from clicking the new database item in a toolbar or menu through filling out the table wizard.

Tables

Tables are the building blocks for databases. Data is stored in them, and records are organized in them. From tables, reports and forms are generated, and queries are performed.

A table is organized into fields and records. A field contains a specific piece of data that is part of a record. Each field is placed in a column.

Table in LibreOffice Base — A table has fields in each column. Each field contains a piece of data for a record. Each row has a different record.

A record is composed multiple fields, and contains all the data about a particular person, business, or item in a table. Records are place in table rows.

The Table Wizard is one of the easiest ways for database and LibreOffice Base beginners to create tables.

Creating a database

Database item in the New menu of the Standard Toolbar — This is the Database item in the New icon in the Standard toolbar.

A new database is like any other new document in LibreOffice. It can be created from any application. The New sub-menu and the New icon are the same in any of the six applications.

Database also is one of the choices in the left column of the StartCenter.

Launch LibreOffice by clicking the StartCenter icon or one of the application icons in your operating system’s menu structure.
Click Database from the Standard tool icon or the New sub-menu in the File menu of one of the applications.

This launches the Database Wizard.

Database Wizard

NOTE: The Document Foundation wants to replace HSQL with Firebird. However, the replacement process has not yet come to fruition. Many users may have to activate experimental features to enable Firebird Embedded in the Embedded Database drop-down menu in the Database Wizard. The Database Wizard is covered below.

The wizard is what allows you to create an Embedded database, open an existing one, or connect to one located on a server.

An embedded database is integrated with an application, such as Base, that requires access to stored data. The database system cannot be easily accessed by the end-user and requires little or no ongoing maintenance. By default, Base allows you to choose either an HSQL database or a Firebird one to create.

Step 1
The three choices in Step 1 of the Database Wizard:

Create a new database
Open an existing database file
Connect to an existing database

This Web page focuses on the first choice.

The drop-down menu associated with this radio button has two choices: HSQLDB Embedded or Firebird Embedded. Click the one you want to use.

Here is a Web page that compares the two.

Click the Next button to go to Step 2.
Step 2

The next page has several choices. Register the database: Clicking the radio button for registering the database.

Data from any database file can be registered to LibreOffice. To register means to tell LibreOffice where the data is located, how it is organized, how to get that data, and more. Once the database is registered, you can use the menu command View – Data source to access the data records from your text documents and spreadsheets.

The next choice is to Open the database for editing.

A check in this box will open the database immediately after you click the Finish button and save the database, so you can create tables and add data right away.
However, if you also put a check in “Create tables using the table wizard,” the table wizard will appear on top of the Base window right after you click the Finish button and save the database. You will use this to create your first table rather than create it from scratch.

Table Wizard

The wizard has four steps:

Select fields
Set types and formats
Set primary key
Create table

Select fields
This step allows you to choose from sample tables, and apply one and its categories to your table. It has four sections:

Category
Sample tables
Available fields
Selected fields

Category

There is a Personal and a Business radio button. The one you choose affects what items are in the Sample tables drop-down menus; business table samples are different from personal table samples. Click one of the radio buttons.

Sample tables

This drop-down allows you to choose a sample table. As just stated, if you chose the Business radio button in Category the drop-down menu will be different than if you chose the Personal radio button.

Business category	Personal category
Tasks Assets Events Orders Contacts Expenses Invoices Payments Products Projects Customers Employees Suppliers Categories Deliveries TimeBilled MailingList OrderDetails Reservations Transactions EmployeesTasks InvoiceDetails	Plants Authors DietLog Library Recipes Accounts Addresses Categories ExercisesLog Investments Photographs CD-Collection DVD-Collection MiniatureFilms HouseholdInventory

Available and Selected fields

When you select s sample table, there will be all the available fields of that table in the Available fields combo box.

The Selected fields combo box is empty until you use the controls between the two combo boxes to move fields from the Available box to the Select box.

There are four arrow buttons. The two that are pointing right move fields into the Selected box.

Single right arrow

The single arrow allows you to move fields from the Available fields box that you have selected. You can click on a single field and just move it. There are two ways to select more than one field.

If the desired fields are on top of one another:
1. Click the top one that you want to move.
2. Hold down the Shift key
3. Then click the field that is directly below it.
4. Repeat that step until all the desired fields are selected.

If the desired fields are NOT on top of each other; they are separated by fields that you do not want to move to the Selected fields box:

NOTE: This will also work for fields that are directly below or above a previously selected field.

1. Click the top one that you want to move.
2. If you are using Window or a Linux distro, hold down the Ctrl key. Mac users need to hold down the Cmd key.
3. Click all the desired fields.

When all the desired fields are selected, click the single right arrow button. All the selected fields will be moved to the Selected fields.

Double right arrow

Clicking this moves all the fields from the Available fields to Selected fields.

Single left arrow

The single arrow allows you to delete selected fields from the Selected fields box. If they were taken from the sample table that is currently showing in the Available fields box, they will be returned to it. that you have selected. You can click on a single field and just move it. There are two ways to select more than one field.

If the desired fields are on top of one another:
1. Click the top one that you want to move.
2. Hold down the Shift key
3. Then click the field that is directly below it.
4. Repeat that step until all the desired fields are selected.

If the desired fields are NOT on top of each other; they are separated by fields that you do not want to move to the Selected fields box:
NOTE: This will also work for fields that are directly below or above a previously selected field.
1. Click the top one that you want to move.
2. If you are using Window or a Linux distro, hold down the Ctrl key. Mac users need to hold down the Cmd key.
3. Click all the desired fields.

When all the desired fields are selected, click the single left arrow button. All the selected fields will be deleted or returned to the Available fields box.

Double left arrow

Clicking this deletes all the fields from the Selected fields box or returns them to the Available fields box.

You can change the sample table and therefore the available fields. And move fields from different sample tables into the Selected fields box.

The fields you put in the Selected box are fields that will be in your table. Click the Next button to move to Step 2.

Set types and formats

Step 2 gives you the opportunity to change the fields LibreOffice has given to you to meet your needs. It lists the Selected fields, from Step 1, in a combo box and several drop-down menus and text boxes under Field information.

Selected fields

The combo box is where you select a field that you want to modify. It also allows you to change the order that the fields will be placed in the table.
To rearrange the fields:

Click a field that you want to move.
Then click the up or down arrow buttons.
- Clicking the down arrow button will move the selected field below the one it is currently above.
- Clicking the up arrow button will move the selected field above the one it is currently below.

NOTE: If the top field is selected, the up arrow button will be inactive. If the bottom field is selected, the down arrow button will be inactive.

To rearrange the fields:

Click a field that you want to move.
Then click the up or down arrow buttons.
Clicking the down arrow button will move the selected field below the one it is currently above.
Clicking the up arrow button will move the selected field above the one it is currently below.

NOTE: If the top field is selected, the up arrow button will be inactive. If the bottom field is selected, the down arrow button will be inactive.

Add a field
Clicking the Plus button below the Selected fields box will add a new field, one that is not from the sample tables in Step 1.

When you click it, a new Field, called Field will be placed in the box. The Field information section explains how to change its settings.

Delete a field

Click the desired field.
Click the Subtract button.
The selected field has now been deleted.

Field information
This is where you change the name of the field, change the datatype that can be entered in it, whether the field must have data in it or not, and the maximum amount of characters that can be entered in it.

Field name:
This text box either has the name from Step 1 or Field. To change the name:

Click on it to highlight it
Type a new name

Field type:
This drop-down menu has 21 datatypes in it.

Field name and entry required are the only field settings that are constant properties, regardless of the field type.
Other properties vary based on the datatype. These settings will appear when you choose a datatype they are associated with. They are as follows:
- Length: This is available to most of the datatypes. The number shown in this field cannot be changed. It reflects the maximum length for the datatype.
- Decimal: This is available to the precise and numeric datatypes. It allows you to set how many places to the right of the decimal point will appear. Type the number of places in the field.
- Default value: This is for the Boolean datatype. It allows you to set whether the field will have a check in it by default or be blank. For a check, choose Yes in the drop-down menu.

You will probably not use most of the datatypes in the drop-down for a database for personal use. Those mostly used are as follows:

Memo [LONGVARCHAR]: This is typically used to store a large amount of text, such as a description field.
Text [VARCHAR]: This is used to a short string of text, such as names or addresses.
Yes/No [BOOLEAN]: This data type is good for yes or no questions about a record. (e.g. Has the bill been paid?).
Time [TIME]: This is for storing the hours, minutes, and seconds that compose a day. The format of the date can be set in the table design view.
Date [DATE]: This is for storing calendar dates. The format of the date can be set in the table design view.
Decimal [DECIMAL]: This datatype allows for a precise number, where mathematical computations can be performed. It allows you to define the number of decimal places.

A description of all the datatypes can be found here.

Entry required
Choose either Yes or No in this drop-down menu. If you choose Yes, data must be entered in this field. Otherwise Base will notify you with an error dialog.

Set a Primary key

This step allows you to create a primary key or set a current field in the table. Each table in a database needs to have a field that is not shared by any other table in the database. Each record in a table will have a unique identifier.

The first thing to do is make sure there is a check in the Create a primary key box. Then you have three radio button for generating a primary key:

Automatically add a primary key
Use an existing field as a primary key
Define primary key as a combination of several fields

Automatically add a primary key
Selecting this radio button will have LibreOffice Base generate a primary key field for the table. If you place a check in the Auto value check box Base will generate an ID number for each record.

The number for each new record will incrementally increase the number. For example, the first record would be A010; the second record would be A011.

Use an existing field as a primary key
This radio button activates the field name drop-down menu. The menu contains fields you added in the first step.

Click the desired field. This radio button also has an Auto value check box, if you want Base to generate a uniques ID.

Define primary key as a combination of several fields
This radio button allows you to choose more than one field that you selected in the first step and use them a primary key. The fields are listed in the Available fields box, and you need to move the selected fields to the Primary key fields box.

Click the top field you want to include. Then perform one of the following series of steps.
If the subsequent fields are directly below the selected field:

Hold down the Shift key on your keyboard.
Then click the desired field that is on the bottom. All the desired fields will be selected.</

If the subsequent fields are NOT directly below the selected field, but separated by fields you don’t want to include:

Hold down the Ctrl key if you are using Windows or a Linux distro. Mac user hold down the Command key.
Click each desired field until all the desired fields are selected.

Next, click the right arrow button that is between the two boxes to move the selected fields.

Click the left arrow to remove fields from the Primary key fields box. Use the above instructions for selecting fields you want to remove.
You also can rearrange the order that the fields will appear in the table.

To rearrange the fields:

Click a field that you want to move.
Then click the up or down arrow buttons.
- Clicking the down arrow button will move the selected field below the one it is currently above.
- Clicking the up arrow button will move the selected field above the one it is currently below.
- NOTE: If the top field is selected, the up arrow button will be inactive. If the bottom field is selected, the down arrow button will be inactive.

Click the Next button when you are finished setting a primary key.

Create table

The final step lets you name or rename the table. It also allows you to do one of of the following:

Insert data immediately
Modify the table design
or Create a form based on this table

Naming
The text box under “What do you want to name your table?” may be blank, or if you chose fields in step 1 from just one sample table, it may have the name of the sample table.
To change the name, click the text box so the current text is highlighted or the cursor is flashing in the text box.

Delete the current name.
Type a new name.

What do you want to do next?
This section of the step allows you to choose one of the three radio buttons.

Insert data immediately: This will open the table, so you can start adding data.
Modify the table design: This opens the table in design view, so you can add fields or change data types.
Create a form based on this table: This will open a blank form document in Writer, LibreOffice’s text editor, and it will launch the Form Wizard.