Altering a Table

The Alter Table dialog helps you alter a table without writing SQL.

Opening the Alter Table Dialog

To alter an existing table:

1. Locate the table node in the Databases tab tree,
2. Select the table node and open the Alter Table dialog from the right-click menu.

The Alter Table dialog is organized in three areas from the top:

• General Table Info Specifies the owning database connection, database and/or schema, and table name. These are picked up from the selection in the tree when the assistant is started.
• 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 shows the SQL statements for altering the table based on your input.

Columns Tab

The Columns tab lists all table columns along with their attributes. The actual columns of the Columns tab is dependant on database. Additional columns may be shown for other databases. An example is Oracle and MariaDB which supports invisible columns and thus a column for setting this is shown.

To add a column:

1. Click the Add button,
2. 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,
3. 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),
4. In the last two fields, 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.

You may find additional fields depending on the features supported by the database you are working with 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.

Settings for generated fields are shown if the database supports automatically inserted values, typically to insert the next available sequence number in a numeric column.

The Create/Alter Table dialog 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/alter the table.

To remove a column:

1. Select a cell in the column row,
2. Click the Remove button.

To move a column to another location (Only supported for Create Table):

1. Select a cell in the column row,
2. Click the Up or Down buttons.

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.

To declare a Primary Key:

1. Optionally enter a constraint name for the primary key constraint in the Constraint Name field,
2. Select the column(s) to be part of the primary key by clicking the checkboxes in the Include field in the columns list.

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 you can optionally declare rules for what to do when a referenced primary key is removed or updated in the referenced table.

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/altered.

To declare a new foreign key constraint:

1. Click the Add button next to the list of foreign keys,
2. Optionally enter a name for the foreign key in the first field in the list,
3. 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,
4. Use the Referenced Table controls to select the table that the foreign key refers to,
5. 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:

1. Select the foreign key in the list in the top section,
2. Click the Remove button.

Unique Constraints Tab

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.

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:

1. Click the Add button,
2. 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 constraint,
3. 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 column order for the constraint with the Up and Down buttons.

To remove an existing constraint:

1. Select the constraint in the list in the top section,
2. Click the Remove button.

Check Constraints Tab

The Check Constraints tab is only available for databases that support this constraint type. A check constraint declares 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, you may see auto-generated check constraints for columns that you marked as not allowing null values in the Columns tab.

To create a check constraint:

1. Click the Add button,
2. Optionally enter a constraint name in the Constraint Name field.
3. 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:

1. Select the constraint in the list,
2. Click the Remove button.

Indexes Tab

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.

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:

1. Click the Add button,
2. 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 constraint,
3. If you want the index columns to have unique values for all rows in the table, click the checkbox in the Unique field,
4. 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 column order for the index with the Up and Down buttons.

To remove an existing index:

1. Select the index in the list in the top section,
2. Click the Remove button.

SQL Preview

The SQL Preview area is updated automatically to match the edits made in the assistant. To show the SQL Preview area check the Show SQL checkbox. 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 alterations, click the Execute button to create it.