Indexes

Database indexes are applied to Tables to improve the performance of data retrieval and sort operations. Multiple indexes can be defined against a Table; however, each index imposes overheads (in the form of processing time and storage) on the database server to maintain them as information is added to and deleted from the Table

In Enterprise Architect an index is modeled as a stereotyped operation.

Some DBMSs support special types of indexes; Enterprise Architect defines these using additional properties such as function-based, clustered and fill-factor.

Access

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

Work on an index

Step	Action	See also
1	On the 'Constraints/Indexes' tab for the Table, right-click and select 'Add new constraint'. The new constraint is added with the default name 'constraint1' and the Type of 'index'. Overtype the name with your preferred index name.
2	Assign the appropriate columns to the Index. The 'Assigned Columns' list has an additional 'Order' field that specifies the order (Ascending or Descending) in which each assigned column is stored in the index. You can toggle the order for each column, as required. Additionally, for MySQL indexes, a 'Len' field will be visible in which you can define Partial Indexes; that is, an index that uses the leading 'n' number of characters of a text based field. The 'Len' field takes only whole number numeric values of between 0 and the column's defined length. A value of 0 (which is the default) indicates that the entire column is to be indexed.	Database Table Constraints
3	In the 'Property' panel, review the settings of the extended properties that are defined for the current DBMS.

Step

Action

See also

On the 'Constraints/Indexes' tab for the Table, right-click and select 'Add new constraint'.

The new constraint is added with the default name 'constraint1' and the Type of 'index'.

Overtype the name with your preferred index name.

Assign the appropriate columns to the Index.

The 'Assigned Columns' list has an additional 'Order' field that specifies the order (Ascending or Descending) in which each assigned column is stored in the index. You can toggle the order for each column, as required.

Additionally, for MySQL indexes, a 'Len' field will be visible in which you can define Partial Indexes; that is, an index that uses the leading 'n' number of characters of a text based field. The 'Len' field takes only whole number numeric values of between 0 and the column's defined length. A value of 0 (which is the default) indicates that the entire column is to be indexed.

Database Table Constraints

In the 'Property' panel, review the settings of the extended properties that are defined for the current DBMS.

Additional Properties

Property	Description	See also
Is Unique	(True/ False) indicates that a Unique index cannot contain more than one instance of a combination of values across a set of columns.
Is Clustered	(True/ False) Indicates the physical order in which the data is stored.
Fill Factor	A numeric value between 0 and 100, that defines the percentage of available space that should be used for data.
Functional-based	A SQL statement that defines the function/statement that will be evaluated and the results indexed; for example: LOWER("field")
Include	Identifies a comma-separated list (CSV) of non-key Columns from the current table.

Notes

Warning: Enterprise Architect assumes that Indexes have at least one column assigned to them; however, Enterprise Architect does not enforce this rule during modeling
If DDL is generated for a Table that has an Index defined without column(s) assigned, that DDL will be invalid, unless the index is functional-based

Any columns assigned to a functional-based index are ignored

The Functional-based extended property will only be visible on Tables where the DBMS supports them; that is, PostgreSQL and Oracle

Learn more