The table editor allows you to view and edit all table settings, such as its Fields, Indexes, Constraints, Triggers and Comments. Each of these items has all settings organized inside tabs on the Table editor, to improve visualization.
To access the table editor, double-click on the desired table on the Project explorer or on a Diagram. When opening a table by double-clicking on a diagram, the Back to diagram button is enabled, so you can easily return to the original diagram.
The fields tab inside the table editor is divided into three sections: Fields list, Field properties tab and Description tab.
The fields list shows all field names and types. You may add, remove or reorder all fields using the icons on the top left of the list. Right-clicking on any part of the list will open a context menu with these same functions, plus a duplicate field option and the option to copy field to clipboard. Copying to clipboard offers three options: List of fields, which will create a comma-separated list of all fields in this table, INSERT command, which will create an insert script to generate a record with all the fields, and UPDATE command, which will create an update script.
All required fields are shown in bold. The vertical key icon identifies fields which are part of the table's primary key. The horizontal key icon/FK text identifies a foreign key related to a parent table.
Selecting the desired field, all of its properties can be edited in the Field properties tab:
•Domain: the domain that will be used to define settings such as type and size of the selected field. All settings automatically loaded from the domain will be filled and their respective editors will be disabled.
•Primary key: defines if the selected field is part of the table's primary key. These keys may be set by selecting the checkbox or through the Indexes tab where specification and ordering of these fields can be done.
•Logic type: defines the concept type of the selected field, usually corresponding to the physical type in the database. All data types supported by the DBMS in use are available for selection. Depending on your choice of logic type, different options will be enabled in the editor. Selecting any Identity type, such as Int (identity) on SQL Server, the Identity section will be enabled, allowing you to set automatic increments on the field.
oSeed: defines the initial number of an auto-increment field.
oIncrement: defines the increment value of an auto-increment field.
•Size: this editor will be enabled when applicable, as for alphanumeric types.
•Precision: decimal and numeric types will enable this editor. It defines precision for these data types.
•Physical type: non-editable. It displays the settings of the physical type applied in the field when the database is generated, based on specifications of logic type, size etc. This editor shows the exact definition of this field on the generated script.
•Not null constraint: it will require a not-null value for the field when inserting or updating a record, making this a required field.
oCheck expr.: a formula for validation / condition that must always be true. For example, the field age could have a check constraint of "Age > 18". It will not be possible to insert a record in this table that does not satisfy this condition. This editor is automatically filled and disabled for changes if an existing domain with a check constraint is selected on the Domain list.
oSpecific: is only enabled when an existing domain with a check constraint is selected on the Domain list. If checked, it allows changes on the expression only applied to this specific field. It's unchecked by default.
•Constraint name: optional information, enabled when an expression is entered on the Check expr. editor.
oDefault value: the field is automatically filled with a specific value when inserting a new record in the table. This editor is automatically filled and disabled for changes if an existing domain with a default value is selected on the Domain list.
oSpecific: is only enabled when an existing domain with a default value is selected on the Domain list. If checked, it allows changes on the value only applied to this specific field. It's unchecked by default.
•Constraint name: optional information, enabled when a value is entered on the Default value editor.
The indexes tab inside the table editor is divided in three sections: Indexes list, Index properties and Index fields.
The Indexes list shows all indexes present on a table. You may add or remove indexes by using the buttons on the top right of the indexes list or by right-clicking anywhere on its area. A key field is identified by the key symbol.
In the Index properties section you may have two data editors:
•Index name: it is always enabled, allowing you to add or change the index name.
•Index type: is only enabled when a non-primary index is selected. Valid options are:
- Non exclusive: Index does not enforce any validation, it's just used for performance
- Exclusive: Index is exclusive (unique) which means it won't allow duplicated values for the index fields
- Unique Key: Index is actually an Unique Key constraint. Exclusive and UniqueKey usually have the same effect, the difference is when index is "Unique Key", it will be created as unique constraints in table (usually the database will create an internal exclusive index to enforce the unique key.
The Index fields section, you may add or remove fields from the index. When clicking Add field to index, all available fields on the table will be shown for your selection. The fields in the index can be ordered by clicking on the heading of the column order, allowing Ascending (default) or Descending options.
Check Constraints Tab
The Check constraints tab inside the table editor is divided in two sections: Constraints list and Constraints properties.
In the Constraints editor you may view all constraints related to the selected table on the Constraints list. By selecting them, you can edit its name and expression in the Constraint properties section. You may add or remove constraints by using the buttons on the top right of the constraints list or by right-clicking anywhere on its area.
The Triggers tab inside the table editor is divided in tree sections: Triggers list, Trigger properties and Implementation.
In the Triggers list, all triggers related to the selected table are listed. Triggers are procedures executed every time an update / insert / delete is executed in a given table. It works as a code programmed in the DBMS language.
In Trigger properties, you may edit the name of this trigger and its description.
In Implementation, the complete command to generate the trigger in the database is shown for edition. When you create a trigger, it automatically implements "CREATE TRIGGER <%TriggerName%> ON <%TableName%>". These macros "<%...%>" are replaced by the trigger's and the table's names, so you can rename both trigger and table without having to update this implementation.