Altova DatabaseSpy 2024 Professional Edition

Design Database Tables

Home Prev Top Next

So far in this tutorial, you have created an empty SQLite database. With DatabaseSpy, there are various ways to create database objects or modify existing ones. This part of the tutorial shows a simple approach to quickly create a new table. The new table will supposedly store some reports, and it should consist of the following columns:

 

The identifier ("id") of the report, of type INTEGER. This column must have a primary key which ensures the value of each row is unique.

The report name ("name"), of type TEXT.

 

Step 1: Create a new table

To create the new table:

 

1.Make sure that the data source is connected dbs_ic_connected in the Project window. Otherwise, right-click the data source, and select Connect.

2.Click the Show in Online Browser dbs_ic_right_arrow_highlight button that appears next to the connected data source. (Alternatively, click the Online Browser tab.)

dbs_tutorial_design_01

3.In the Online Browser, right-click the main dbs_ic_database database, and select Create Table from the context menu. This action has the following outcome:

 

A dialog box appears and informs you that DatabaseSpy has created a change script for the current action. Click OK to dismiss the dialog box.

A new table called "newtable" appears with the "Tables" folder. Notice that the table has the dbs_ic_newicon overlaid, which indicates that it is a new uncommitted database object. Likewise, the database has the dbs_ic_changedicon overlaid, which indicates that some of its child objects have uncommitted changes.

No changes are committed to the database at this stage. The database change script generated so far is displayed in the Database Structure Change Script Window.

dbs_tutorial_design_02

 

4.Select the table and press F2 to rename it to "reports". You can also right-click the table and select Rename table from the context menu. You will notice that the change script is updated accordingly.

 

By default, the script will keep changing as you are changing the structure of the database in subsequent steps; therefore, you might want to select the Don't show this message again option.

 

Step 2: Add columns

Each time when you create a new table from DatabaseSpy, it has one column by default. Recall that, in this example, the requirement was that the first column must be named "id" and it should be of type INTEGER. It must also be a primary key. To achieve this, take the following steps:

 

1.Select the column and then press F2 to enter the new name ("id").

2.To change the column's type, first select the column and then change the Type property to INTEGER in the Properties Window.

3.Also, make sure to clear the Nullable property. This makes it possible to add the primary key to the column in a subsequent step.

dbs_tutorial_design_03

With other database types, you might want to set additional properties of the column, like Size or Auto-increment. The database used in this example is a SQLite database, so it does not need an Auto increment property explicitly, because the first column in the table is of type INTEGER and has a primary key.

 

4.To add the primary key on the "id" column, right-click the column and select Create Primary Key from the context menu. The outcome is that a "Keys" folder is added to the table, which stores the new primary key.

dbs_tutorial_design_04

 

The new primary key has the name <generated> by default; this indicates that the key name will be generated by the database when you commit the script. Note that, with SQLite databases, the name of primary keys is always shown as <generated> in DatabaseSpy, even after the script is committed to the database. Other database kinds may support changing the <generated> name to a custom name and displaying it as such after the script was committed, see also Reference of Generated Names.

 

Using the same steps as above, add a new column called "name" of type TEXT.

 

Step 3: Commit the change script

The table design created so far has not been committed to the database yet. To commit the changes to the database, click the Execute the Generated SQL Change Script dbs_ic_execute_script button  in the Database Structure Change Script window.

dbs_tutorial_design_05

DatabaseSpy runs the script against the database, and informs you about the result. The new table is now visible in the Online Browser.

dbs_tutorial_design_06

This concludes the design part of this tutorial. For more in-depth information about designing database objects, see Managing Database Objects.

© 2017-2023 Altova GmbH