Table Editor

Previous  Index  Next  How To Use

Base Tables, usually called Tables, can be created, altered and dropped from the Table Editor. Besides the table object, you can also insert, edit and delete table data in this editor.

 

If your database system supports Domains (User Defined Datatypes) or Object Types, these will be available in the Column Type drop down box. You can create a Domain or Object Type while editing a table and it will show up in the list of available types for the column.

 

Below is an example, taken from the Firebird Table Editor:

The Firebird Table Editor

 

As you can see in the above example, tables can have additional options at an Options tab. For example, the primary key constraint name is available on that tab if you select any columns to be included in the primary key constraint. By default, the name is defined by a Name Template.

 

You cannot drop a primary key or remove columns from the primary key in this editor. To do this, use the Constraint Editor.

 

If your database engine supports it, a Column Description box let's you edit a user-written column description.

 

If a column has specific options for the given data type, on the left there will be the possibility to expand and see the additional column options.

 

Adding/Dropping/Editing Columns

You can add and drop columns by using the Add Column or Delete Column buttons. If a column is already part of the table in the database, you will get a warning saying the column isn't new and Database Workbench will ask for a confirmation.

 

As long as a column is new (not saved to the database yet), you can edit all properties - if it's an existing column, what you can edit depends on the database engine capabilities to modify existing columns.

 

If the table is still new (not saved to the database yet) or if the database engine supports moving column positions in a table, you can move columns up and down with the arrows in them.

 

You can easily duplicate an existing column by using the Duplicate Column button. For InterBase and Firebird, this will show additional options in a Duplicate Column dialog.

 

Editing & Viewing Data

The Data tab shows the table data, by performing a simple "SELECT * FROM table" SQL statement. Changes on this tab will not be saved unless you commit the transaction that is started when you modify data.

 

Clicking on the header of a column will sort the data by that column name, additionally, there's also a filter row where you can edit a SQL-based filter for each column.

 

Columns will display a red asterisk if the table field is defined as "not null".

 

This tab also has a toolbar button to export or print the table data.

 

Table Editor - Data Tab
Data tab on the Table Editor

 

Options

Depending on the database engine capabilities, the Options tab holds multiple things.

 

At least one thing that it holds (except for MySQL), is the box to edit the primary key constraint name.

 

Sample Table Options page (in this case: Firebird)

 

For more information on the available options, see:

InterBase Table Options
Firebird Table Options
MS SQL Server Table Options
MySQL Table Options
Oracle Table Options
NexusDB Table Options
PostgreSQL Table Options
SQLite Table Options

 

Duplicate Column

When using InterBase or Firebird, using the Duplicate Column button shows the below dialog, for other database systems, it will simply create a copy of the column.

 

Duplicate Column dialog

 

In addition to simply duplicating the current column, you can create the following for the new column:

Create UPPER triggers

Creates a BEFORE UPDATE and BEFORE INSERT trigger that uppercases the value in the given column. Optionally uses the original column as the source to always update this column. This can be useful to implement a so-called "shadow" column for upper case searches.

Create ASCending Index

Creates a single column ascending index on the given column.

Create DESCending Index

Creates a single column descending index on the given column.

 

Column Specials

When using InterBase or Firebird, the toolbar shows an additional button "Column Specials". If a column has a small * next to it on the left, it has one or more "column specials" defined.

 

By using the Column Specials dialog, you can quickly create additional table objects, below is an explanation of each item. Depending on the datatype of the column, different items may be enabled.

 

The Column Specials dialog

 

Create UPPER triggers

Creates a BEFORE UPDATE and BEFORE INSERT trigger that uppercases the value in the given column.

Create ASCending Index

Creates a single column ascending index on the given column.

Create DESCending Index

Creates a single column descending index on the given column.

Create AutoIncrement Trigger & Generator

Creates a generator and a BEFORE INSERT trigger that checks for NULL/0 to see if a value should be used from a generator.

 

All additional objects are named according to the naming templates in the Preferences.