Creating a Unique Index

See Also

Create a unique index when uniqueness is a characteristic of the data itself, but the combination of indexed columns is not the same as the table’s primary key. For example, if you plan to query frequently on the Social Security number (ssn) column in the employee table (where the primary key is emp_id), and you want to ensure Social Security numbers are unique, create a unique index on ssn. If the user enters the same Social Security number for more than one employee, the database displays an error and cannot save the table.

Tip   Although a unique index will help locate information, for the best performance results it is recommended that you use primary key or unique constraints instead. For more information about these constraints, see Primary Key Constraints and Unique Constraints.

When you create or modify a unique index, you can set an option to ignore duplicate keys. If this option is set and you attempt to create duplicate keys by adding or updating data that affects multiple rows (with the INSERT or UPDATE statement), the row that causes the duplicates is not added or, in the case of an update, discarded. Neither the new value nor the original value of the row that would produce the duplicate exists in the updated table.

For example, if you try to update "Smith" to "Jones" in a table where "Jones" already exists, you end up with one "Jones" and no "Smith" in the resulting table. The original "Smith" row is lost because an UPDATE statement is actually a DELETE followed by an INSERT. "Smith" was deleted and the attempt to insert an additional "Jones" failed. The whole transaction can’t be rolled back because the purpose of this option is to allow a transaction in spite of the presence of duplicates.

Caution   If you do not set the Ignore duplicate keys option and you try to update a row in a way that creates a duplicate key, the original row is discarded. The new value rather than the original value of the row that would produce the duplicate is retained in the updated table. This transaction cannot be rolled back.

To create a unique index

1. In your database diagram, select the table you want to index.
   
   
2. Right-click the table, and choose Properties from the shortcut menu.
   
   
3. Choose the Indexes/Keys tab.
   
   
4. Choose New. The Selected index list displays the system-assigned name of the new index.
   
   
5. Under Column name, select the columns you want to index. You can select up to 16 columns. For optimal performance, select only one or two columns per index.
   
   
6. Select the Create UNIQUE check box.
   
   
7. Select the Index option.
   
   
8. Select the Ignore duplicate keys option if you want to ignore new or updated data that would create a duplicate key in the index (with the INSERT or UPDATE statement).

The index is created in the database when you save the table or diagram.

Note   You cannot create a unique index on a single column if that column contains NULL in more than one row. Similarly, you cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row. These are treated as duplicate values for indexing purposes.

Note   Not all databases work with indexes in the same way. For more information, see Database Server Considerations, or consult your database documentation.