A unique index does not allow any two rows to have the same index value, including NULL. If data already exists, the system checks for duplicate values when the index is created and subsequently 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 SAP ASE 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 attempt to insert the same row during two concurrent sessions on a DOL table with a unique index, the first session fails with error number 2601. SAP ASE fails the insert to avoid blocking, and leaves the database in a consistent state. Additionally, a blocked second session could impact concurrency.
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.
You can use the unique keyword on composite indexes.