What is the difference between a primary key and a unique key in a relational database system?

1 Answers
Answered by suresh

Primary Key vs Unique Key in RDBMS

Difference between Primary Key and Unique Key in a Relational Database System

In a relational database system, a primary key and a unique key serve similar purposes of enforcing data integrity and ensuring uniqueness of values, but there are key differences between them:

  1. Primary Key:
    • Primary key is a column or a set of columns that uniquely identifies each record in a table.
    • It must contain unique values and cannot have NULL values.
    • There can be only one primary key in a table.
    • Primary key automatically creates a unique index on the column(s).
    • Primary key is used to establish relationships between tables in a database.
  2. Unique Key:
    • Unique key is a column or a set of columns that ensures the values are unique across the table.
    • It allows NULL values, but only one NULL value is allowed (except in MySQL).
    • There can be multiple unique keys in a table.
    • Unique key also creates a unique index on the column(s).
    • Unique key constraints are used to enforce the uniqueness of values, but not necessarily as identifiers.

Both primary key and unique key play essential roles in maintaining data integrity and relational database design, but their use cases and constraints differ based on the specific requirements of the database schema.

Answer for Question: What is the difference between a primary key and a unique key in a relational database system?