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 description 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 the selection of columns for use in constraint creation (including Primary Key) and checks 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 varies depending on server vendor and version.

The Comments tab provides space for text comments that will be stored with the table in the database. Some vendors also allow Table Column Comments, to make comments on specific columns.  Where this is available, such comments will also appear ER Models containing tables with these properties.

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 10.1. Because other databases may not have dimensions, steps 6 and 7 may not be needed.

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 varies 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 that 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.