Design Database Tables

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Tutorials > "Nanonull" Database >

Design Database Tables

So far in this tutorial, you have created an empty SQLite database. With DatabaseSpy, you can design the tables either visually in the Design Editor, or by running table creation scripts in the SQL Editor. This part of the tutorial shows you how to design a table visually. The new table will supposedly store some reports, so it should consist of the following columns:

 

The identifier (id) of the report (of type INTEGER)
The report name (of type TEXT).

 

 

To create the new table:

1.Make sure that the data source is connected (green) in the Project window. Otherwise, right-click the data source, and select Connect.
2.On the File menu, click New | Design Editor (alternatively, press Ctrl+D or click the Design Editor dbs_ic_design_editor toolbar button). The Design Editor is now displayed in the main pane of DatabaseSpy.
3.Right-click anywhere in the Design Editor, and select Create New Table (Ctrl+T) from the context menu. An information dialog box appears, notifying you that a database change script has been created. Click OK to dismiss the message. Remember that the change script is visible at all times in the Database Structure Change Script window, and it is being updated on the fly as you make changes in the Design Editor window.
4.In the Design Editor, double-click inside the table header, and enter a descriptive name for the table, for example "reports".

dbs_tutorial_design1

The first column of the table, id, is generated automatically by DatabaseSpy, so all you need to is change the data type, as follows:

 

Click the id column, and type INTEGER under "Type".

dbs_tutorial_design2

Note:By default, DatabaseSpy creates automatically the primary key for new tables. To disable this option, select the menu command Tools | Options | Design Editor, and then clear the option Automatically create primary key for new tables.

 

 

To create the second column:

1.Right-click the table in Design Editor, and select Insert New Column from the context menu.
2.Type the name of the column (reportname), and type TEXT under "Type".
3.Optionally, clear the Nullable check box (assuming you don't want the report name to be optional). This can be done either directly in the table design or in the Properties window.

 

So far, you have created the table design; however, it has not been committed to the database yet. To commit the changes to the database:

 

1.Make the Database Structure Change Script window active (you can find it under the main pane).

dbs_tutorial_design3

2.Click Execute the Generated SQL Change Script dbs_ic_execute_script.

 

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

dbs_tutorial_design4


© 2019 Altova GmbH