A unique index permits no two rows to have the same index value, including NULL. The system checks for duplicate values when the index is created, if data already exists, and checks each time data is added or modified with an insert or update.
Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. For example, you would not want a unique index on a last_name column, because there is likely to be more than one “Smith” or “Wong” in tables of even a few hundred rows.
However, a unique index on a column holding social security numbers is a good idea. Uniqueness is a characteristic of the data—each person has a different social security number. Furthermore, a unique index serves as an integrity check. For instance, a duplicate social security number probably reflects some kind of error in data entry or on the part of the government.
If you try to create a unique index on data that includes duplicate values, the command is aborted, and Adaptive Server displays an error message that gives the first duplicate. You cannot create a unique index on a column that contains null values in more than one row; these are treated as duplicate values for indexing purposes.
If you try to change data on which there is a unique index, the results depend on whether you have used the ignore_dup_key option. See “Using the ignore_dup_key option”.
You can use the unique keyword on composite indexes.
