Defining Indexes
The Table Editor Indexes tab displays the table’s indexes (sort orders).
Indexes
The list on the left is the indexes overview that lists the numbered indexes belonging to the table. Use this list to create a new index, delete an existing index, or select an index to view or edit its composition.
Selecting an index displays its column segments and index attributes. The column segments are displayed in the Index Segments grid on the right. The full set of index attributes are displayed in the Properties Panel.
Toolbars
Add Index
Click this button to create a new index for the table. Embedded database tables may define up to 16 indexes. Other database types may support more indexes.
Keyboard shortcut: (Ctrl+A)
Delete Index
Click this button to delete the currently selected index in the Indexes overview list.
Keyboard shortcut: (Ctrl+D)
Promote Index
This only applies to SQL databases. This decreases the index number of the selected index (e.g., if the selected index was 2, it will change it to 1).
Demote Index
This only applies to SQL databases. This increases the index number of the selected index (e.g., if the selected index was 1, it will change it to 2).
Index Segments
The Index Segments grid, on the right, displays an ordered list of columns that make up the index segments for the selected index. These segments determine the sorting order for rows when using this index.
The ordering of the columns is important. The data is sorted by the first column, then the second column, third column, etc. All rows with the same value in the first index segment column will then be sorted by the value of the second index segment column, then third, etc.
The following operations can be performed in this list:
- Change the column name for the currently highlighted segment. To do this, drop down the combo list and select the table column you would like to use in this segment, or simply type the column name. As you type, Table Editor will select the first column that matches the name you have typed so far.
- Add a new segment to the index. To add a new segment, click the 'Add Segment' button on the toolbar or go to the bottom row of the list and begin selecting columns.
- Remove a segment from the index. To remove a segment, select it in the list and click the 'Delete Segment' button on the toolbar.
- Adjust the position of a segment in the list. To move a segment up or down, select the segment you want to move, then click the 'Promote Segment' or 'Demote Segment' toolbar buttons. Note: Changing the position of an index segment affects the ordering of rows for that index.
A given column can be used in more than one index, but for each column, there can be only one Main Index. See the discussion under Main Index.
Selecting an index segment displays its attributes in the Properties Panel.
Toolbars
Descending
If you check descending, the segment will rank from high to low, rather than the default low-to-high. For example, the ASCII value "Pears" would rank before "Apples".
This is useful, for example, when you want to list customers in a customer table with the largest sales value first. Here you would check descending for the Sales_Value segment of the index.
Ignore Case
This option can be checked for ASCII-type index segment columns only. If you check Ignore Case, all letters ('a'-'z') in column values will be treated in the index as being capital letters.
For example, if one column contains the data "Smith, Robert", it will be the exact equivalent (for indexing purposes only) of a column containing "SMITH, ROBERT". This eliminates the sort of confusion users experience when capitalization of letters in the data affects the order in which rows are listed or found.
Index Name
This only applies to SQL databases. The index name is typically created by native SQL tools or the SQL drivers. They are meaningful and include acronyms, such as "PK" for Primary Key, for example, "PK_Album" for the primary key for the album table of the open-source Chinook database. The index name can be manually edited; each index name should be unique for each table.
Index Type
This only applies to SQL databases. Index type can be Server, Server Only, or Client Only:
- Server: Exists on Server, defined in .INT file; modification will require restructure.
- Client Only: Defined only in .INT file; modification will require .INT file update.
- Server Only: Exists on Server, not defined in .INT file; modification will change to Server and require restructure. These indexes should not be referenced in source code, as their numbers can dynamically change.
Key index attributes, such as Clustered, Primary Key, and Unique, are displayed as text below the Index Type.
Other Attributes
A complete list of index attributes for the selected index or the selected index segment is shown in the Properties Panel. These are comprised of both editable and read-only attributes. The attribute list and the allowed values are determined by each database driver type.
Add Segment
Click this button to add a new segment to the selected index's segment list. Embedded database tables support up to 16 segments per index. Other database types may support more or fewer segments.
Use the combo list to select one of the table columns for the new segment.
Keyboard shortcut: (Ctrl+A)
Insert Segment
Click this button to add a new segment above the currently selected segment. Use the combo list to select one of the table columns for the new segment.
Keyboard shortcut: (Ctrl+I)
Delete Segment
Click this button to delete the selected segment from the currently selected index.
Keyboard shortcut: (Ctrl+D)
Promote Segment
Click this button to move the currently highlighted segment up in the index's segment list.
Demote Segment
Click this button to move the currently highlighted segment down in the index's segment list.