[ Master documentation index ]
Introduction
The Create Table and Index Assistants are used to create new tables and
indexes. The assistants are quite simple to use since they examine
various meta data in the database (depending on what assistant is
used) and then let the user point and click to define the actual table
or index. Both assistants finally generate the appropriate SQL and
pass it over to the SQL Commander which is used to execute it.
These utilities are launched from the Database
main menu or in the Database Objects
tree right click menu. The menu choices are enabled only if a table or
index can be created for the selected node in the Database Objects tree.
Note: Remember to manually
select the Reload right click
menu choice in the Database Objects tree once a new table has been
created.
Create Table
Start the table creation assistant by choosing the Create Table action in the objects tree right click menu or from the Actions menu. Make sure
you have located and selected the appropriate object in the Database Objects tree as this
selection is used to define which database, schema, etc. the table
will be created in.
Figure: The right
click menu in the Database Objects tree
The Create Table assistant is organized in three
areas from the top:
- Table Info
Specifies the owning database connection, database and/or schema. These
are picked up from the selection in the tree when the assistant is
started. Table name is empty and must be specified.
- Columns
The list is used to organize the columns that will be in the final
create statement. The Data Type column is a list of supported data
types for the actual database. Columns can be re-ordered using the main
controls.
- SQL Preview
The SQL previewer instantly shows the SQL that is used to create the
table.
Figure: The table
assistant
Columns
Add columns by using the Edit->Insert menu choice and Edit->Delete to remove the
currently selected row. They can be re-organized using the Edit->Move Up and Edit->Move Down menu operations.
The assistant is based on generic JDBC and it is the responsibility of
the user to enter the required fields i.e. specifying size for text
data types, ignore size for some BLOB types, enter scale for decimal
types, etc.
The Data Type field for a column when selected contains a list of valid
data types for the actual database connection.
Figure:
Data Type list (for MySQL)
The Size and Scale fields are used either in conjunction or else the size field on its own. Size is often used to set the max length of
text columns. It is also used in combination with the Scale field when
defining decimal boundaries.
Figure:
Size and scale for a DECIMAL data type
The above example will allow a total length
(including the decimal places) of 7. Examples:
1.02
9871.1
8172.0
18291.22
12.112 <- Error
1921211.11 <- Error
SQL Preview
The SQL Preview area is updated automatically to match the edits made
in the assistant. The preview is read only.
Figure: The SQL
Preview for a table
Execute
When you are
satisfied with the table then choose File->Pass
SQL to SQL Commander menu choice. The SQL is then inserted into
the SQL Commander. Now select Database->Execute
main menu choice to execute it as per any regular SQL. If any errors
appear during the execution then go back to the Table Assistant and make the necessary changes.
Note: If you want to refine the
setup of a table then just select the already visible Table Assistant
window. Do not choose the Create Table menu choice again since it will then
clear the assistant from its current setup.
Create Index
The Create Index Assistant is much the same as the Create Table
Assistant. It is launched from the same menus and the overall layout
and controls are the same. The only difference is the Columns list which now lists the
columns that will be part of the index. The Column field when selected lists the
current columns in the table.
Figure: The Index
creation assistant
The example shows that a new unique
index, UniqueName will be
created for the MyFirstTable.
It will index the Name column
in Ascending order.
Please read the previous sections on how to use the Table assistant to
edit and create the actual index.
Copyright © 2006 Onseven Software AB. All rights reserved.
