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 |
Develop > Data Modeling > Database Builder > Click on Table name > Constraints/Indexes | Right-click | Add New Constraint |
Context Menu |
In diagram | Right-click on Table | Features | Constraints/Indexes | Right-click | Add New Constraint |
Keyboard Shortcuts |
Click on Table: > Constraints/Indexes: |
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 . |
|
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 re-arrange 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 ( ) or
- Move column '<name>' down ( )
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).