Two Jobs At Once
A unique index does everything a normal index does and adds a rule: no two rows may share the same indexed key. Most databases enforce a uniqueness or primary key constraint by building a unique index behind it. The index is both the lookup structure and the enforcement mechanism.
How Enforcement Works
On every insert or update, the database checks the index for an existing entry with the same key before allowing the write. If one exists, the write is rejected with a violation error. Because the index is sorted, this check is a fast seek, not a scan.
The Null Subtlety
Uniqueness and nulls interact carefully. In standard SQL, null is not equal to null, so multiple null values are usually allowed in a unique index even though duplicate real values are not. Composite unique indexes and database specific options can change this behavior, so it pays to check.
Composite Uniqueness
A unique index can span multiple columns, enforcing that the combination is unique rather than each column alone. For example a pair of tenant id and email can be unique together while either column repeats on its own.
Key idea
A unique index speeds lookups and enforces uniqueness by rejecting writes whose key already exists, which is how constraints are backed, with nulls usually allowed to repeat and composite indexes enforcing uniqueness on the column combination.