Index, Trigger, Check Constraint
What is an Index?
An index is a sorted look-up for a table. When it is known in advance that a table must be sorted in a specific order, it is usually worth the small processing overhead to always maintain a sorted look-up list rather than sort the table every time it is required. In Enterprise Architect, an index is modeled as a stereotyped operation. On generating DDL, the necessary instructions for generating indexes are written to the DDL output.
The unique characteristic applied to a single column ensures that no two data values in the column can be identical. The unique stereotype applied to an index ensures that no two combinations of values across a set of columns can be identical.
What is a Trigger?
A trigger is an operation automatically executed as a result of the modification of data in the database, and usually ensures consistent behavior of the database. For example, a trigger might be used to define validations that must be performed every time a value is modified, or might perform deletions in a secondary table when a record in the primary table is deleted. In Enterprise Architect, a trigger is modeled as a stereotyped operation.
What is a Check Constraint?
A Check Constraint enforces domain integrity by limiting the values that are accepted by a column.
Create an Index
Ensure that the column(s) to be used in the index have already been defined in the table.
- Right-click on the required table either in a diagram or in the Project Browser.
- Select the Operations context menu option. The Operations dialog displays.
- Add an operation (with a name such as IDX_CustomerID; the IDX_ prefix is optional but it helps identify the operation).
- In the Stereotype field for the operation, select index (check and unique are also supported).
- Click on the Column tab.
- Select the required columns from the Columns drop-down list in the required order, then click on the Save button to save changes.
Create a Check Constraint or Trigger
- Locate the required table in either a diagram or the Project Browser.
- Use the context menu to open the Operations dialog.
- Add an operation (such as CHK_ColumnName or TRG_OnCustomerUpdate; the CHK_ and TRG_ prefixes are optional but help identify the operation).
- In the Stereotype field for the constraint, select check or trigger as appropriate and click on the Save button to save changes.
- Select the constraint operation, then the Behavior tab.
- Enter the entire check constraint clause (for example, col1 < 1000), or the entire trigger code (including the CREATE_TRIGGER statement) in the Initial Code field and click on the Save button to save changes.
The example below shows how an index looks in a diagram (in the Order element):