Adding Tables Using Design Editor

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

Home >  Tutorials > "ZooDB" Database > Adding Tables to the Database >

Adding Tables Using Design Editor

The Design Editor can also be used to add tables to databases using a graphical user interface. Use of the Design Editor requires no knowledge of SQL syntax. To illustrate this, we will now add to the database a new table that stores zoo visitor statistics. We will call this table tblVisitorStatistics. The table will consist of the following columns:

 

StatisticID: int; not null; primary key
StatisticDate: datetime; not null;
VisitorCount: int; not null

 

 

To add the table to the database using the Design Editor:

1.Click the Design Editor ic_new-design-editor button in the Standard toolbar. A new Design window, called Design1, is opened with the connection name and database name visible in the Status bar.

design_new-editor

2.Click the New Table ic_create-new-table button. A new table appears in the Design Editor window. A change script message appears at this point, and the SQL statements appear in the Database Structure Change Script window. After you complete the table definition, you will need to execute the change script to actually create the table in the database.

dbs_tutorial_new_table1

3.Double-click the table name, "table (dbo)", and enter "tblVisitorStatistics". Press Enter.

dbs_tutorial_new_table2

4.In the table design, double-click the text under "Column". Change the column name to "StatisticID" and press Enter.

dbs_tutorial_new_table3

5.Likewise, double-click the text under "Type", replace "varchar(255)" with "int", and press Enter. At this stage, the table has one column, and we want to add two more.
6.Click the plus icon ic_add-column next to column StatisticID. A column is added and selected for editing. Type "StatisticDate", press Tab, enter type as "date", and press Enter.
7.Right-click into the table design and choose Insert new | Column from the context menu. Change the column name to "VisitorCount"and press Enter. Using the same technique as for previous columns, change the data type of the "VisitorCount" column to "int".
8.Right-click the title bar of the Columns section and activate the Identity option in the context menu. The select the Identity check box for the StatisticID column.

 

At this stage, the table design should look as follows:

dbs_tutorial_new_table4

Notice that a primary key having the name <generated> has been generated automatically for the first column in the table. The indicates that the new column will have a primary key with a name automatically assigned by the database, when you run the database update script. Optionally, if you want to change the name of the primary key, replace the text <generated> with a descriptive primary key name. In this case, however, you will need to ensure that the name is not already used in the database.

 

You are now ready to execute the database script which will actually add the table to the database. In the Database Structure Change Script window, click the Execute Change Script ic_execute-sql button to commit the new table to the database.

dbs_tutorial_new_table5


© 2019 Altova GmbH