Prev Next

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.

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.

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.

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.

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