Differences Between a Unique Constraint and a Unique Index

Although a unique index and a unique constraint are functionally similar, besides various differences in the syntax by which you declare, alter, or destroy them, there are additional differences between these two types of database objects:

• In DDL statements, they are registered or dropped in different tables of the system catalog
• In DML statements, enabled unique constraints on a logged table are checked at the end of a statement, but unique indexes are checked on a row-by-row basis, thereby preventing any insert or update of a row that might potentially violate the uniqueness of the specified column (or for a multiple-column column constraint or index, the column list).

For example, if you stored the values 1, 2, and 3 in rows of a logged table that has an INT column, an UPDATE operation on that table that specifies SET c = c + 1 would fail with an error if there were a unique index on the column c, but the statement would succeed if the column had a unique constraint.