Understanding the Key Differences: Unique Index, Index, Unique Constraint, and Primary Key in SQL

Here’s a breakdown of the differences between Unique Index, Index, Unique Constraint, and Primary Key:

1. Index

• Purpose: Speeds up data retrieval by optimizing queries on specific columns.
• Behavior: Indexes store pointers to data in a sorted manner, which allows the database engine to find rows more efficiently.
• Uniqueness: Regular indexes are not unique by default.
• Use Case: Used when you frequently query specific columns and want faster lookups (e.g., for large datasets).

Example:

CREATE INDEX idx_userid ON user(userid);

• Creates a non-unique index on the userid column to speed up queries involving this column.

2. Unique Index

• Purpose: Ensures that the values in the indexed columns are unique across all rows in the table.
• Behavior: Same as a regular index, but with the additional guarantee that duplicate values are not allowed.
• Uniqueness: Enforces uniqueness of the values in the indexed columns.
• Use Case: Used when you need fast lookups and also want to ensure data integrity by preventing duplicate values.

Example:

CREATE UNIQUE INDEX uidx_userid ON user(userid);

• This ensures that the userid column has no duplicate values and also optimizes lookups on this column.

3. Unique Constraint

• Purpose: Ensures that a column (or a combination of columns) contains only unique values. It’s a logical constraint for data integrity.
• Behavior: Backed by a unique index under the hood (automatically created by the database).
• Uniqueness: Enforces uniqueness on the column(s).
• Use Case: Used when you want to ensure data integrity by preventing duplicate values in certain columns, but without the special role of a primary key.

Example:

ALTER TABLE asset ADD CONSTRAINT ukey_user UNIQUE (tenant, userid);

• This ensures that the combination of tenant and useruserid is unique across the table.

Note: The main difference between a Unique Index and a Unique Constraint is mostly semantic, with a constraint focusing more on data integrity, while an index focuses on performance.

4. Primary Key

• Purpose: Defines the primary identifier for each row in a table. Every table can have only one primary key.
• Behavior: A primary key constraint is a unique constraint with the additional requirement that the columns cannot contain NULL values.
• Uniqueness: Enforces uniqueness and non-nullability on the column(s).
• Use Case: Used to uniquely identify each row in the table and establish relationships with foreign keys in other tables.

Example:

ALTER TABLE asset ADD CONSTRAINT pk_user PRIMARY KEY (userid);

• This ensures that the userid column is unique and that it contains no NULL values. It also serves as the table's primary identifier.

A primary key is essentially a unique constraint that also ensures no NULL values and is used as the main row identifier.