Managing Tables & Objects

Manage Table

This window is used for administering tables. Display this window from the Database Explorer by selecting the Create Table, Properties of Table, Alter Table or Redefine Table functions. This is part of the Administration Component of AQT.

With this window you are able to:

• Create a new table

• Display the full definition of a table.

• Alter a table. This is used to amend the definition of the table

• Redefine a table. This is used to make a significant change to the definition of a table. In order to do this, the table is dropped and recreated with your new definition. Security rights, and other related objects, are reestablished. However the table contents will be lost. You must save your data before running this function.

The appearance of the window will be different for different database types, and whether you are in Create, Display, Alter or Redefine modes. The screenshot below is an example of the Manage Table window for creating a DB2 table.

There are many parts to a table definition, as indicated by the different Tabs on this window:

• the columns

• other properties of the table

• index, keys, triggers and other related objects

This window cannot be used for specifying a table's authorities. For this, use the Manage Authorities window.

Run

Once you have entered / altered the table definition, click on Run. This runs the SQL for creating, altering or redefining your table. If this involves running more than one SQL statement you will be shown the Run Multiple Statements window.

You can also run the SQL with F5.

If you are in evaluation mode of the Admin Component, this button will not be active.

Preview SQL

This displays the SQL which AQT has generated for creating, altering or redefining your table. You can save this SQL to disk or copy it to the Run SQL window (though you can't of either of these if you are in evaluation mode of the Admin Component).

Other notes

Copying the definition from an existing table

If you click on the Copy From button, AQT will copy the table definition from another table. This is described more fully in the next section.

Specifying the Table Schema

This is selected by using the drop-down list (this contains existing table schemas). If you wish to create the table in a new Schema, type the name into the schema box.

Copying the definition of an existing table

Copying the definition of an existing table

Often, when you are creating a new table, you wish to copy the definition of an existing table. This is generally done for two reasons:

• you are creating a table which is very similar to an existing table. In this case it is useful to use the existing table as a starting point for the new table definition.

• you are creating a table which has the same definition as a table in another database

To copy an existing table definition, click on the Copy From button and select the table you wish to copy. Note that the Copy From button will only be visible when you are in Create mode.

You will be asked Do you want to copy Indexes, Keys and other related objects? If you reply No, AQT will just copy the column-list and table properties. If you reply Yes, all attributes of the table will be copied, including primary key, indexes, foreign keys, views, aliases, triggers and authorities.

Names of Indexes and other Related Objects

For many database types, the names of the Primary Key, Indexes, Foreign Keys, Constraints, Views, Aliases and Triggers are unique throughout the database. If you copy these definitions from another table, you will need to change the name of these objects before you create them. If you do not do this, they will fail to be created as objects with these names already exist.

Cross-Database Copies

You can copy the definition of a table from either the same database or another database. The other database can be of a different type. For instance, you can create a table on Oracle, copying the definition from a SQL Server table. When you do this, AQT will:

• convert the data types between the databases

• convert the definitions of the table between the databases

• convert the definitions of indexes, primary keys, foreign keys and other objects

In most cases this is only partially successful. Most databases have attributes which aren't present in other databases. For instance, when copying the definition from SQL Server to Oracle, the tablespace, logging, percent free and other Oracle attributes will not be set, as SQLServer tables have no such corresponding attributes.

Change Table/Column Names

When copying a table definition from another database, the table and column names will come across in style as per the database you are copying from. Often you may wish to change to another style of name.

If you go Edit > Change Table/Column Names, you have the option of changing the table and column names to another style.

The following is the example of these styles for column CUSTOMER_CODE:

This only changes the names of your tables and columns; the names of your primary keys, indexes etc are not changed.

Specifying a table's general properties

Clicking on the General tab will show you the general properties of the table.

Managing Indexes, Keys and other parts of a table definition

A table also has a number of "Related Objects" such as Indexes, Views, Triggers etc. These are shown on the other tabs in the Manage Table window.

In all of these cases (except for Primary Key), a table may have multiple of these objects. Consequently, the displays are split into two halves:

• the left half lists gives a list of the objects

• the right half displays the details of the selected object

This is shown in the following screenshot, which is for Indexes.

In create, alter and redefine modes some buttons will be displayed below the object list:

• New - creates a new object of that type. Once you have created a new object, it will be added to the object list.

• Delete - deletes the object. In create and redefine modes, the object will be removed from the object list. In alter mode, the object will be marked as pending deletion.

• Modify. Click on this to modify the object. In alter mode, you will only see this button if it is a new object - once you have created the object (eg. after you have clicked on Run), it cannot be modified.

When you click on New or Modify, you will be taken to the Manage Object window for that object.

Alter Table

You can Alter the definition of a table by (in the Database Explorer window) clicking on a table then the Alter button in the Admin Toolbar.

You will be shown a window such as the following:

You will not be able to amend every part of a table's definition. Different databases have different rules about which parts of the table definition can be altered.

• attributes that you are able to change will be in white. Non-changeable values are in grey.

• once you have amended a value, it will be shown in yellow. This indicates a pending change.

• if your database allows you to delete columns, you will have a button Delete Column - click on this to mark the column for deletion. A pending deletion is shown in yellow with strike-through font. If you subsequently click the column, the Undelete Column button will be displayed. Clicking on this will take the column out of delete-pending status.

• clicking on Preview shows you the SQL that AQT will run for applying the changes that you have made.

Applying the Changes

Click on Run to run the changes.

• AQT will apply the changes to the database by running a series of SQL statements.

• AQT keeps track of whether each change is successful. If so, the value you have changed is taken out of change-pending status (and returns to being white). Any columns or objects you have deleted are removed from the grids.

• If the change is not successful, the change remains in change-pending or delete-pending status; you can then correct your changes and click on Run again.

• Can click on Refresh to re-load the table definition. This can be useful after you have made as series of changes.

Manage Object

Use this window to administer objects other than tables (tables are administered in the Manage Table window). This window is part of the Administration Component of AQT.

The Manage Object window is a single window that is used for managing many different types of objects. It will take on different appearances depending on the type of object you are dealing with. In addition, the window will have a slightly different appearance depending on whether you are creating, altering or displaying properties of the object.

Some particular objects are described in the following sections:

The remainder of this section gives general information about the use of the Manage Object window.

Invoking the Manage Object window

Open the Manage Object window from the Database Explorer:

• select the object (any object other than a table)

• click on the Create, Properties or Alter buttons in the Admin toolbar

You also invoke this window from the Create/Alter Table window, when you select New or Modify for a related object (such as a primary key, index, foreign key etc).

Help with Administering your database

The Manage Table and Manage Object windows make it very easy for managing the objects in your database. However AQT doesn't provide you detailed help about this, or with the meaning of various parameters you can specify when creating objects. If you need any help with these, consult your database documentation.

Buttons

Some of the buttons you will see on the window are as follows:

Altering Objects

When you are altering an object, the window works as described in Alter Table.

Manage Index or Primary Key

This window is used for creating, displaying and altering indexes and primary keys. It is one of the forms of the Manage Object window. That section gives general information about this window, including the use of the buttons and menu items.

This window is part of the Administration Component of AQT.

You can activate this window in the following ways:

• from the Database Explorer, click on Create, Properties of or Alter of an Index or Primary Key.

• from the Manage Table window, click on New from the Index or Primary Key tabs.

The window consists of two parts:

• general parameters of the index / primary key

• index columns

General Properties of the Index / Primary Key

This shows you the miscellaneous properties of the index or primary key. These will include:

• Table. This specifies the table which the index / primary key is based on. Use the Browse (. . . ) button to select the table.

• Unique. This specifies whether the index is unique or not.

You may see other properties here, depending on your database type.

Index Columns

You use the Index Columns to specify the columns in your index or primary key.

• On the left will be a list of the columns in your table, on the right is a list of the columns in your index or primary key.

• Specify that a column is to part of the index column list by either double-clicking it, or selecting it then clicking on the > button.

• Remove a column from the index column list by either double-clicking it, or selecting it then clicking on the < button.

• Change the order of the columns in the index column list by dragging them up or down.

• For indexes you can specify whether an index column is going to be an Ascending or Descending Index. This can not be specified for a primary key, for which the column order is not relevant.

More info on Primary Keys

• Primary Keys have a name (which you specify at the top of the window). The is often referred to as the Constraint name.

• Most databases do not allow you to create a primary key on columns that can be null. In other words, all columns in your primary key must be defined as not null.

• Almost all databases will implement the primary key by creating a unique index. This index is generally given the same name as the constraint name.

• If you want more control about how this index is created (for instance you need to specify particular parameters such as space allocation), you should create the index first, then create the primary key. Most databases will see that you already have an index of the right definition and will use this as the primary-key index, rather than creating a new one.

• Some databases (such as DB2 z/OS) will not create an index when you define the primary key. Instead, a unique index on the primary key columns must already exist before you create the primary key.

Manage Foreign Key

This window is used for creating and displaying foreign keys. It is one of the forms of the Manage Object window. That section gives general information about this window, including the use of the buttons and menu items.

This window is part of the Administration Component of AQT.

You can activate this window in the following ways:

• from the Database Explorer, click on Create > Foreign Key.

• from the Manage Table window, click on New from the Foreign Key tab.

The window consists of two parts:

• properties of the foreign key

• foreign key columns

What is a Foreign Key?

This described in Understanding Foreign Keys.

Properties of the Foreign Key

To create a foreign key you need to specify the following properties.

• Table. This is the source table for the foreign key relationship.

• Referenced Table. This is the table which is referenced by the foreign key constraint. AQT will assume that the referenced columns will be the primary key columns of this table; these will be given in the right of the bottom grids. If the table does not have a primary key, or you wish to use different columns, click on Specify Key to select these.

• Delete Rule. This specifies what happens when a row is deleted from the target table. This is explained in more detail below.

• Update Rule. This specifies what happens when one of the referenced-columns is updated in the target table. This is explained in more detail below.

• Include Ref Columns. This specifies whether the SQL for creating the foreign key is to include the list of referenced columns. If the referenced columns are the primary key of the referenced table, this column list is optional.

Delete and Update rule can take the following values (depending on your database).

• Restrict. This will prevent the row from being deleted / updated if there are some rows referring to it. In the AQT_Demo example, this means you will not be able to delete a customer from the Customer_Details table while there are some orders (in Order_Detail) that use the customer.

• Cascase. This will delete the row and all the rows that refer to it. In the AQT_Demo example, if you delete a customer from the Customer_Details table, your database will also delete all orders (in Order_Detail) that use the customer.

• No Action. Same as restrict.

• Set Null. This will allow the row to be deleted / updated, any columns that refer to it are set to null. In the AQT_Demo example, if you delete a customer from the Customer_Details table, all orders (in Order_Detail) that use the customer will have their cust_code column set to null.

Specifying the Foreign Key Columns

You use the bottom three grids to specify which columns in the source table map to the columns in the referenced table.

The right grid will have the referenced columns. This will be either the primary key of the Referenced Table, or the columns you specify with Specify Key. You must now specify the Foreign Key columns, which are the columns from the source table that match the referenced columns.

The columns in the source table are given in the left grid. You select or de-select these as being Foreign Key columns by double-clicking them or using the < and > buttons.

MySQL

There are restrictions on creating foreign keys in MySQL.

Manage Procedure or Trigger

This window is used for creating, displaying and altering a procedures, triggers, functions and some other objects. It is one of the forms of the Manage Object window. That section has general information about this window, including the use of the buttons and menu items.

This window is part of the Administration Component of AQT.

You can activate this window in the following ways:

• from the Database Explorer, clicking on Create, Properties of or Alter of a Procedure or Trigger.

• from the Manage Table window, clicking on New from the Trigger tab.

The following is an example of the window for creating a DB2 stored procedure:

The window consists of three parts:

• general properties of the procedure or trigger

• the procedure Parameters

• text of the procedure or trigger

General Properties of the Procedure or Trigger

Displays the miscellaneous properties of the procedure / trigger. The properties you see here will be different depending on your database type.

Procedure Parameters

Click on the Parameters tab to enter the procedure parameters (eg. the parameters used when invoking the stored procedure).

This tab is only visible for procedures (and not functions or triggers), only for some databases. Also, for Oracle, it is only displayed for Create Procedure and not for Alter Procedure.

Text of Procedure or Trigger

This has the text of your trigger or stored procedure.

When you are creating a procedure, AQT will generate the header part of the procedure definition (eg. the Create Procedure clause). In the Procedure Text, you only need to enter the body of your procedure.

If you in doubt as to what AQT is generating versus what you have to enter, click on Preview SQL.

AQT doesn't provide any help with the syntax of creating stored procedures - for this you will need to consult the documentation for your database.

Warning - Altering a Procedure, Trigger or Function

When you alter a Procedure, Trigger or Function, AQT will do this by dropping the existing object then creating it with your new definition. If the Create fails, you will no longer have the object in your database.

In this case, do not exit this window or hit Refresh - otherwise the object will be gone forever.

You must correct the definition so that the object is created successfully. If you cannot do this, do a Preview and save the Create statement in a file to correct at a later time.

This warning does not apply to Oracle (for which AQT uses Create or Replace Procedure) or SQL Server (for which AQT uses Alter Procedure).

Manage View

This window is used for creating, displaying and altering views (and summary tables). It is one of the forms of the Manage Object window. That section has general information about this window, including the use of the buttons and menu items.

This window is part of the Administration Component of AQT.

You can activate this window in the following ways:

• from the Database Explorer, select a View, then Create, Properties of, or Alter

• from the Run SQL window, click on Create > View.

• from the Manage Table window, click on New from the View tab.

The window has three parts, described in sections below:

• general properties of the view

• view text

• view columns

General Properties of the View

Shows the miscellaneous properties of the view, such as Description and Check Option. The properties you see will differ depending on your database type.

When creating or altering a view, you will also see a checkbox Include Column List. This specifies whether the column list is to be included in your view definition. This is discussed under View Columns.

View Text

Displays the query that your view is based on, which will vary depending on where Manage View was started:

• if you started Manage View from the Database Explorer, and had a table selected, the view text will be select * from tablename.

• if you started Manage View from the Run SQL window, your query text will be used as the view text

• if you started Manage View from the Manage Table window, the view text will be select * from tablename

You can amend this view text.

View Columns

View Columns are the names of the columns in your view. By specifying these, your view can have different column names than the column names in your query.

When creating or altering a view:

• when you check Include Column List, the view columns will be specifed in your view definition. This gives you the ability to have view columns which are different from your query columns.

• if you do not have Include Column List the view columns will not be specified in your query; your view columns will be the same as your query columns. If your query returns columns of the same name, or with no name, your database may give you an error (different database have different rules here). The circumvention to this is to specify a column list.

• clicking on Load from View Text will set the view columns to the names of your query columns. This is often a good starting point for setting the names of your view columns. You should always run Load from View Text after changing your query so that it returns different columns.

• Load from View Text will run your query (from a technical point of view, it just does a Prepare). If there is a syntax error in your view query, you will get an error message and the view columns will not be loaded.

Warning - Altering a View

When you alter a View, AQT will do this by dropping the existing view then creating it with your new definition. If the Create fails, you will no longer have the view in your database.

In this case, do not exit this window or hit Refresh - otherwise the view will be gone forever.

You must correct the definition so that the view is created successfully. If you cannot do this, do a Preview and save the Create statement in a file to correct at a later time.

This warning does not apply to Oracle (for which AQT uses Create or Replace View) or SQL Server (for which AQT uses Alter View).

Summary Tables

This window is also used for managing DB2/UDB Summary Tables. A Summary Table, also called a Materialised Query Table, is a table which is based on the result of a query. It is much like a view, except that the data actually exists (unlike a view, which is just a definition).

Defining a Summary Table is very similar to defining a view, so much of the discussion above still applies.