Database Table Constraints/Indexes

Within Enterprise Architect, Table Constraints and Indexes are modeled on the same screen; collectivity they are referred to as Constraints. Database Constraints define the conditions imposed on the behavior of a database Table. They include:

Primary Key - uniquely identifies a record in a Table, consisting of one or more columns
Index - improves the performance of retrieval and sort operations on Table data
Unique Constraints - a combination of values that uniquely identify a row in the Table
Foreign Key - a column (or collection of columns) that enforce a relationship between two Tables
Check Constraints - enforces domain integrity by limiting the values that are accepted by a column
Table Trigger - SQL or code automatically executed as a result of data in a Table being modified

In Enterprise Architect, you can define and maintain Table Constraints using either the purpose-designed 'Constraints/Indexes' page of the Database Builder or the Columns and Constraints screen.

Access

Ribbon	Specialize > Tools > Database Builder > Click on Table name > Constraints/Indexes \| Right-click \| Add New Constraint
Context Menu	In diagram \| Right-click on Table \| Features & Properties \| Constraints/Indexes \| Right-click \| Add New Constraint
Keyboard Shortcuts	Click on Table: F9 > Constraints/Indexes: Ctrl+N

Create a Constraint

The process of creating any of these constraint types is the same and is achieved in one of the ways described here.

Create a Constraint - Using the context menu or keyboard

Step	Action	See also
1	A new constraint is automatically created and assigned the default name constraint n (where n is a counter) and a 'Type' of 'index'. Overtype the default name with your own constraint name.
2	If necessary, in the 'Type' field click on the drop-down arrow and select the appropriate constraint type.
3	If you prefer, type an alias for the constraint, in the 'Alias' field. The 'Columns' field is read-only; it is populated with the columns that you assign to the 'Involved Columns' tab.

Step

Action

See also

A new constraint is automatically created and assigned the default name constraint n (where n is a counter) and a 'Type' of 'index'.

Overtype the default name with your own constraint name.

If necessary, in the 'Type' field click on the drop-down arrow and select the appropriate constraint type.

If you prefer, type an alias for the constraint, in the 'Alias' field.

The 'Columns' field is read-only; it is populated with the columns that you assign to the 'Involved Columns' tab.

Create a Constraint - Overtype the template text

Step	Action	See also
1	On the 'Constraints/Indexes' tab for the selected Table, the list of constraints ends with the template text New Constraint. Overtype this text with the appropriate constraint name, and press the Enter key.
2	The new constraint is automatically created and assigned the default Type of index. If necessary, in the 'Type' field click on the drop-down arrow and select the appropriate constraint type.
3	If you prefer, type an alias for the constraint, in the 'Alias' field. The 'Columns' field is read-only; it is populated with the columns that you assign to the 'Involved Columns' tab.

Step

Action

See also

On the 'Constraints/Indexes' tab for the selected Table, the list of constraints ends with the template text New Constraint.

Overtype this text with the appropriate constraint name, and press the Enter key.

The new constraint is automatically created and assigned the default Type of index.

If necessary, in the 'Type' field click on the drop-down arrow and select the appropriate constraint type.

If you prefer, type an alias for the constraint, in the 'Alias' field.

The 'Columns' field is read-only; it is populated with the columns that you assign to the 'Involved Columns' tab.

Assign Columns to a Constraint

The constraint types of Primary Key, Foreign Key, Index and Unique all must have at least one column assigned to them; this defines the columns that are involved in the constraint.

Step	Action	See also
1	On the 'Constraints/Indexes' tab for the selected Table, click on the constraint to which you are assigning columns.
2	The 'Available Columns' panel lists all columns defined for the Table. For each column to assign to the constraint, right-click on the column name and select 'Assign column <name>'. The column name is transferred to the 'Assigned Columns' list.

Step

Action

See also

On the 'Constraints/Indexes' tab for the selected Table, click on the constraint to which you are assigning columns.

The 'Available Columns' panel lists all columns defined for the Table.

For each column to assign to the constraint, right-click on the column name and select 'Assign column <name>'.

The column name is transferred to the 'Assigned Columns' list.

Unassign Columns from a Constraint

Step	Action	See also
1	On the 'Constraints/Indexes' tab for the selected Table, click on the constraint from which you are unassigning columns.
2	In the 'Assigned Columns' list, right-click on the name of the column to unassign from the constraint and select 'Unassign column <name>'. The column name is transferred to the 'Available Columns' list.

Step

Action

See also

On the 'Constraints/Indexes' tab for the selected Table, click on the constraint from which you are unassigning columns.

In the 'Assigned Columns' list, right-click on the name of the column to unassign from the constraint and select 'Unassign column <name>'.

The column name is transferred to the 'Available Columns' list.

Reorder the Assigned Columns in a Constraint

If you have a number of columns in the constraint, you can rearrange the sequence by moving a selected column name one place up or down the list at a time. To do this:

Right-click on the column name to move and select either:
- Move column '<name>' up (Ctrl+Up Arrow) or
- Move column '<name>' down (Ctrl+Down Arrow)

Delete a constraint

To delete a constraint you no longer require, right-click on the constraint name in the list on the 'Constraints/Indexes' tab and select the 'Delete constraint <name>' option. If all validation rules for the given constraint type are met, the constraint is immediately removed from the repository along with all related relationships (if there are any).

Learn more