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
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.