8.2 Creating Tables

Aqua Data Studio contains GUI tools for visually editing and creating Tables.

CREATE

Creating tables through the GUI Tools available in the schema browser begins with opening a database server connection and expanding the tree to view the server's tables. Right clicking on the schema container for Tables generates the pop-up menu containing "Create Table" as its first item.

The Create Table dialog has General, Constraints, Storage and Preview SQL tabs across the top. The General tab is where the table is named and its columns and datatypes are set. The Column information displays more details about the selected columns in the descriptin area below the column grid. INSERT and DELETE of columns is available via right click on the Column Names within the General tab of CREATE and ALTER dialogs. All of the datatypes available to the current database can be accessed with a dropdown next to the column names.

The Constraints tab allows selection of columns for use in constraint creation (including Primary Key) and check conditions. Constraint properties details such as Name, Type, Ref Schema, Ref Table and Check Condition are modifiable visually.  Constraint Definition Table Columns and Referenced Columns can be visually edited. For more on constraints, see the Creating Constraints page.

The Storage tab provides details on Tablespace and table storage, its content vary depending on server vendor and version.

The Comments tab provides space for text comments that will be stored with the table in the database.

The Preview SQL tab displays the DDL used to generate the table and its properties based on the settings of the other tabs.

Below are the steps used to create a table in DB2 9.5. Because other databases may not have dimensions, steps 6 and 7 may not be needed.

1. Connect to Server	2. Right Click on Tables	3. Set General Properties	4. Create Constraints	5. Set Storage Properties	6. Create Dimensions	7. Dimension Summary

8. Add Comments	9. Preview SQL

Once a table has been created, it is possible to use Aqua Data Studio's Visual Editing Tools to alter its properties.

ALTER

Altering tables through the GUI is also done with a right click menu on table objects in the schema browser. Right clicking on a table object opens the same popup menu as above, but to ALTER a table, select the second item in the list, "Alter Table".

The resulting Alter Table window contains General, Constraints, Storage, Permissions and Preview SQL tabs across the top.

Within the General tab, the table name, column names, datatypes and allow nulls can be changed. The Column information is sortable, expandable and displays more details about the selected columns. INSERT and DELETE of columns is available via right click on the Column Names within the General tab of CREATE and ALTER dialogs. The database and schema cannot be changed, and remain grayed out. Columns can be added (INSERT), deleted (DELETE) and changed (ALTER).

Constraints and check conditions can be altered in the Constraints tab. Constraint properties details such as Name, Type, Ref Schema, Ref Table and Check Condition are modifiable visually.  Constraint Definition Table Columns and Referenced Columns can be visually edited Storage information, just as above, can be altered in the Storage tab.

The Storage tab provides details on Tablespace and table storage, its content vary depending on server vendor and version.

Table Permissions (when privileges to do so have been granted to the current user in the server registration Permissions tab) can be altered and granted on the Permissions tab for column and command. Full grant options are available and all Permissions can be set by Grantee Type for Users, Groups or  Roles depending on database vendor and type. Grant, Grant with Grant Option, Add Grant, Add Grant with Grant Option, and Revoke are available for SELECT, INSERT, UPDATE, DELETE across all databases. Depending on vendor supported functionality, there are other permissions which may be set such as ALTER, INDEX, References, On Commit Refresh, Query ReWrite  The "Columns..." button at the bottom right of the Permissions tab, allows further permissions changes, including SELECT, INSERT, UPDATE,  and References for the columns highlighted in the permissions grid within the Permissions tab. 

The Preview SQL tab displays the DDL used to alter the table based on the properties set within the other tabs.

Below are the steps to alter a table.

1. Right Click Alter Table	2. Select Columns to Alter	3. Alter Column Properties	4. Alter Permissions Selected	5. Alter Permissions Altered	6. Alter Permissions Column Permissions	7. Preview SQL

DROP

A right click on an existing table in the schema browser allows a table to be dropped.