What is the difference between primary key and unique key?

by

Primary key and unique key both are the essential constraints of the SQL, but there is a small difference between them

Primary key carries unique value but the field of the primary key cannot be Null on the other hand unique key also carry unique value but it can have a single Null value field.

In SQL, both primary keys and unique keys are used to enforce the uniqueness of values in a column or a set of columns, but there are some key differences between them:

  1. Purpose:
    • Primary Key: A primary key is used to uniquely identify each record in a table. It must contain unique values and cannot have NULL values. Each table can have only one primary key.
    • Unique Key: A unique key is used to ensure that the values in a column or a set of columns are unique across the table. Unlike a primary key, a unique key can allow NULL values, and a table can have multiple unique keys.
  2. NULL Values:
    • Primary Key: A primary key column cannot have NULL values.
    • Unique Key: Unique keys can have NULL values. However, if a unique key consists of multiple columns, the combination of those columns must be unique, allowing NULL values in individual columns.
  3. Number of Keys:
    • Primary Key: A table can have only one primary key.
    • Unique Key: A table can have multiple unique keys.
  4. Indexing:
    • Primary Key: By default, a primary key creates a clustered index, which determines the physical order of data in a table.
    • Unique Key: A unique key creates a non-clustered index by default.
  5. Auto-incrementing:
    • Primary Key: Often used with auto-incrementing or identity columns to generate unique values automatically.
    • Unique Key: Can be used with or without auto-incrementing columns.

In summary, while both primary keys and unique keys serve to enforce uniqueness, a primary key is specifically designed to uniquely identify each record and is more restrictive in terms of null values and the number of keys allowed per table. Unique keys, on the other hand, are more flexible and allow for the enforcement of uniqueness across columns with the possibility of having NULL values.