Symptoms
When you select the 'Unique' checkbox on a Dictionary Entry record in order to create a unique index on that column and capture that change in an Update Set, promoting that Update Set to a target instance does not create the unique index on the target instance.
Cause
The 'Unique' checkbox on the Dictionary Entry record is NOT intended to be used from the UI. It might create an unique index but it DOES NOT create a sys_index_* record in the Customer Update table [sys_update_xml]. When the creation of that Dictionary Entry is captured in an Update Set and that Update Set is promoted to the target instance, the unique index on that column is not created on the target instance.
By default, the 'Unique' checkbox is not added on the Dictionary Entry form, hence, this is NOT the recommended usage.
Unique Indexes MUST be created via the Table form [sys_db_object] ('Tables' module or 'Tables & Columns' module) so that a sys_index_* record is created in the Customer Update table. Subsequent commit of the Update Set on target instance will create the unique index.
Resolution
Steps to follow in order to create a Table Index:
- Navigate to: System Definition > Tables
- Search and access the table on which you want to create the index.
- Navigate to the 'Database Indexes' related list.
- Click on 'New'.
- In the Index Name field, type a name for your index.
- Use the slush bucket to select the fields you want to be included in the index.
The order in which you select the fields affects how the index works. If you do not have expertise in database design, we suggest you consult someone who does.
- To create a unique index, check the 'Unique Index' check box.
- Click on the 'Create Index' UI Action.
The above steps are also available in the following documentation chapter: Create a table index