What's the difference between a Primary Key and a Unique Key?

The primary key and unique key are both essential constraints in relational databases, but they serve different purposes and have distinct characteristics.

Primary Key

A primary key is a column or a set of columns in a table that uniquely identifies each row in that table. The primary key enforces entity integrity by ensuring that no two rows have the same primary key value and that the primary key column(s) do not contain NULL values. Here are the key features of a primary key:

• Uniqueness: Each value in the primary key column(s) must be unique across the table.
• Non-nullability: Primary key columns cannot contain NULL values.
• Single Instance: A table can have only one primary key.
• Clustered Index: In many database systems, defining a primary key automatically creates a clustered index, which organizes the data in the table based on the primary key values.
• Foreign Key Reference: Primary keys can be referenced by foreign keys in other tables to establish relationships between tables.

Unique Key

A unique key, like a primary key, ensures that the values in a column or a set of columns are unique across the table. However, it has some differences from a primary key:

• Uniqueness: Each value in the unique key column(s) must be unique across the table.
• Nullable: Unique key columns can contain NULL values, but only one NULL value is allowed per column.
• Multiple Instances: A table can have multiple unique keys.
• Non-clustered Index: Defining a unique key typically creates a non-clustered index...