Create and Alter Table

• Introduction
• Create Table
  • Columns tab
  • Primary Key tab
  • Foreign Keys tab
  • Unique Constraints tab (database-specific)
  • Check Constraints tab (database-specific)
  • Indexes tab (MySQL only)
  • SQL Preview
  • Execute
• Alter Table

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->Selected Object 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 Oracle)

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, or start typing the data type name to find it and select it with the Enter key. The list contains the names of all data types the database supports.

Figure: Data Type list (for Oracle)

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
     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 find additional fields depending on the features supported by the database you create the table for and the data type for the current column. The Collation field is shown for character columns if the database supports the declaration of a collation for textual data.

Figure: Collation control

The Auto Increment field, and possibly Start With and Increment By fields, are shown for numeric fields if the database supports automatically inserting the next available sequence number in a numeric column.

Figure: Auto Increment controls

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 the 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 Add button 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 corresponding choices in the preview area right-click menu.

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->Selected Object 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 that 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.

---