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.

1. Right-click on the required table either in a diagram or in the Project Browser.
2. Select the Operations context menu option. The Operations dialog displays.
3. Add an operation (with a name such as IDX_CustomerID; the IDX_ prefix is optional but it helps identify the operation).
4. In the Stereotype field for the operation, select index (check and unique are also supported).
5. Click on the Column tab.
6. 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

1. Locate the required table in either a diagram or the Project Browser.
2. Use the context menu to open the Operations dialog.
3. Add an operation (such as CHK_ColumnName or TRG_OnCustomerUpdate; the CHK_ and TRG_ prefixes are optional but help identify the operation).
4. In the Stereotype field for the constraint, select check or trigger as appropriate and click on the Save button to save changes.
5. Select the constraint operation, then the Behavior tab.
6. 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):