[ Master documentation index ]
Introduction
The Create Table, Create Index and Alter Table Assistants are used to create new tables and
indexes and to alter existing tables. The assistants are quite simple to use since they examine
various metadata in the database (depending on which assistant is
used) and then let you point and click to define the table
or index.
The assistants are launched from the Database
main menu, from in the Database Objects
tree right-click menu, or from the Actions menu button in the object view. The menu choices are enabled only if a table or
index can be created for the selected node in the Database Objects tree.
Create Table
To create a table, select an appropriate node in the objects tree,
typically a Tables node, and launch the Create Table assistant from one
of the menus as described earlier.
Figure: The right-click menu in the Database Objects tree
The Create Table assistant is organized in three
areas from the top:
- General 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 is set to a default name that you can change to the real table name.
- Table Details
A number of tabs where you specify information about the columns and,
optionally, various constraints. The Columns, Primary Key and Foreign
Key tabs are available for all databases. The remaining tabs are
database-specific and depends on the features supported by the database
engine.
- SQL Preview
The SQL previewer instantly shows the SQL statement for creating the
table.
Figure: The Create Table
assistant
Just enter as much information as is needed to describe the table and click Execute to create the table.
Columns tab
The Columns tab lists all table columns along with their attributes.
Figure: The Columns tab (for MySQL)
Add columns by clicking on the Add button, and remove the
currently selected column by clicking on the Remove button. You can reorganize the columns using the Up and Down buttons.
Enter the name of the column in the first field and select a data type
from a drop down list in the second field. The list contains the names
of all data types the database supports.
Figure:
Data Type list (for MySQL)
For some data types, such as character types, you may also specify a
size, i.e., the maximal length of the value. For others, like the
decimal types, you can may specify both a size and a scale (the maximal
number of decimals).
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
The last two fields let you specify if the table is nullable and a
default value to use for rows inserted into the table without
specifying a value for the column.
Below the column list, you may see one or two additional fields,
depending on the features supported by the database you create the
table for. The fields are enabled when you select a column that they
apply to. The Collation field is enabled for character columns if the
database supports the declaration of a collation for textual data. The
Auto Increment field is enabled for numeric fields if the database
supports automatically inserting the next available sequence number in
a numeric column.
The Create Table assistant uses database metadata to try to enable only
the fields that apply to the selected data type, but please note that
it is not always possible. For instance, there is no metadata available
to tell if a data type requires, or allows, a size. If you don't enter
a required attribute or enter an attribute that is unsupported for a
data type, you will get an error message when you click Execute to
create the table.
Primary Key tab
The Primary Key tab contains information about an optional primary key
for the table. A primary key is a column, or a combination of columns,
that uniquely identifies a row in a table.
Figure: Primary Key tab
You can, optionally, enter a
constraint name for the primary key constraint in the Constraint Name
field. Select columns to be part of the primary key by clicking the
checkboxes in the Include field in the columns list.
You can change the order of the columns in the key by selecting a column and move it using the Up and Down buttons.
Foreign Keys tab
In the Foreign Keys tab, you can declare one or more foreign keys for
the table. A foreign key is a column, or a combination of columns, that
refer to the primary key of another table. Foreign keys are used by the
database to enforce integrity, i.e., that there is a row in the
referenced table with a primary key that matches the foreign key value
when a new row is inserted or updated, and can optionally declare rules
for what to do when a referenced primary key is removed or updated in
the referenced table.
Figure: Foreign Keys tab
The tab has the following sections:
- A list of foreign keys.
- Controls for selecting the table the currently selected foreign
key refers to, including the database (catalog) and/or schema for the
table.
- A list of all columns for the table being created.
To declare a new foreign key constraint, click the Add
button next to the list of foreign keys. You can then enter a name for
the foreign key in the first field in the list, and select On Delete
and On Update actions from the pull-down menus. The pull-down lists
include all actions that the database support, typically CASCADE,
RESTRICT, NO ACTION and SET NULL. The Columns field is read-only and
gets its value automatically when you select which columns to include
in the key later.
Next, use the Referenced Table controls to select the table that the foreign key refers to.
Finally, check the Include checkbox for all columns in the column list
that should be part of the foreign key and then select the
corresponding column in the referenced table from the pull-down menu in
the Referenced Column field. You can change the column order for the
key with the Up and Down buttons.
To remove an existing foreign key, select it in the list in the top section and click the Remove button.
Unique Constraints tab (database-specific)
The Unique Constraints tab is only available for databases that support
this constraint type. A unique constraint declares that the columns in
the constraint must have unique values in the table.
Figure: Unique Constrains tab
The top portion of the tab holds a list of all unique constraints, and the lower portion holds a list of all table columns.
To create a constraint, click the Add
button and optionally enter a constraint name in the Constraint Name
field. The Columns field in the constraints list is read-only, filled
automatically as you include columns in the constrain. Select the
columns to be part of
the constraint by clicking the checkboxes in the Include field in the
columns list.
You can change the order of the columns in the constraint by selecting a column and move it using the Up and Down buttons.
To remove an existing constraint, select it in the list in the top section and click the Remove button.
Check Constraints tab (database-specific)
The Check Constraints tab is only available for databases that support
this constraint type. Check constraints declare
that a column value fulfills a certain condition when a row is inserted
or updated. Some databases uses check constraints to enforce
nullability rules, so when you alter a table (as described later), you
may see auto-generated check constraints for columns that you marked as
not allowing null values in the Columns tab.
Figure: Check Constrains tab
To create a check constraint, click the Add
button and optionally enter a constraint name in the Constraint Name
field. Enter the condition for the column in the Condition field. You
can use the same type of conditions as you use in a SELECT WHERE clause.
To remove an existing constraint, select it in the list and click the Remove button.
Indexes tab (MySQL only)
The Indexes tab is only used for the MySQL database, as a replacement
for the Unique Constraints tab. The reason is that for MySQL, the
CREATE TABLE statement can be used to declare both unique and
non-unique indexes. MySQL also does not make a clear distinction
between a unique constraint (a rule, most often enforced and
implemented as an index by the database) and a unique index (primarily
a database structure for speeding up queries, with the side-effect of
ensuring unique column values), as most other databases do.
Figure: Indexes tab
The top portion of the tab holds a list of all indexes, and the lower portion holds a list of all table columns.
To create an index, click the Addbutton
and optionally enter a name in the Constraint Name
field. The Columns field in the constraints list is read-only, filled
automatically as you include columns in the constrain. If you
want the index columns to have unique values for all rows in the table,
click the checkbox in the Unique field.
Select the columns
to be part of
the index by clicking the checkboxes in the Include field in the
columns list. You can change the order of the columns in the constraint by selecting a column and move it using the Up and Down buttons.
To remove an existing constraint, select it in the list in the top section and click the Remove button.
SQL Preview
The SQL Preview area is updated automatically to match the edits made
in the assistant. The preview is read only, but you can copy the SQL to the SQL Commander and flip between
formatted and unformatted views using the two buttons in the toolbar
above the preview area.
Execute
When you are
satisfied with the table declaration, click the Execute button to create it.
Alter Table
To alter a table, select the table node in the objects tree and launch the Alter Table assistant from the Database
main menu, the Database Objects
tree right-click menu, or from the Actions menu button in the object view.
Figure: The Alter Table assistant
The Alter Table assistant has exactly the same layout as the Create
Table assistant, with all information about the table you wish to alter
shown when you launch it. As you make changes, such as adding a column,
the SQL Preview area shows the corresponding ALTER TABLE statements.
See the Create Table section for descriptions of all parts of the assistant.
The controls, such as the fields, pull-down menus and buttons, in the
assistant are only enabled if the ALTER TABLE statement for the
database holding the table provides a way to alter the corresponding
table attribute. For instance, for a database only allows the size of a
VARCHAR column to be altered, the Size field in the Columns tab is
disabled for all columns with other data types. If you find that you
can not make the change you want, it is because the ALTER TABLE
statement does not allow that change to be made.
Create Index
The Create Index assistant helps you create an index for a table.
Select the table node in the objects tree and launch the Create Index
assistant from the Database
main menu, the Database Objects
tree right-click menu, or from the Actions menu button in the object view.
Figure: The Create Index
assistant
The Create Index assistant is organized in three
areas from the top:
- Table Info
Specifies the owning database connection, database and/or schema, and
the table name. These
are picked up from the selection in the tree when the assistant is
started. The Index Name field is empty and must be
specified. If you want the index columns to have unique
values for all rows in the table, click the checkbox under the Index
Name field.
- Columns
The list is used to select the columns include in the index.
- SQL Preview
The SQL previewer instantly shows the SQL that is used to create the index.
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 Column field has a pull-down menu with the names all columns in the table. Columns can be re-ordered using the main
controls. Use the radio buttons to select a sort order for the column in the index.
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 an index
Execute
When you are
satisfied with the index declaration, click the Execute button to create it.
Copyright © 2007 Onseven Software AB. All rights reserved.
