Database Table Constraints

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 the purpose-designed Constraints page of the Database Builder or the Columns and Constraints screen. The Table for which you are creating the constraint must have one or more columns assigned in it.

Access Tools | Database Builder | Click on Table name > Constraints | Right-click | Add New Constraint (Ctrl+N)       or

In diagram or Project Browser | Right-click on Table | Features & Properties | Operations > Constraint | Add New Constraint     or

In diagram or Project Browser | Click on Table: F10

Create a Constraint

The process of creating any of the above 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' 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' 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' 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' 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