What are the differences between PRIMARY and UNIQUE constraints in a database?

• PRIMARY Key:

  • Ensures the uniqueness of values in a column (or set of columns).
  • Automatically creates an index to speed up searches.
  • Does not allow NULL values in the column (or any column in the set).
  • There can be only one PRIMARY Key in a table.
  • Used for the unique identification of each row in the table.

• UNIQUE Constraint:

  • Ensures the uniqueness of values in a column (or set of columns).
  • Automatically creates an index to speed up searches.
  • Allows NULL values (but only one NULL, since NULL is not considered equal to another NULL in the context of uniqueness).
  • A table can have multiple UNIQUE constraints.

The main difference is that a PRIMARY Key is an entity identifier in the table and does not allow NULL, whereas UNIQUE simply enforces the uniqueness of values, allowing NULL. The PRIMARY Key is essentially a special case of a UNIQUE constraint with an added NOT NULL restriction.