In database management systems, a Primary Key is a candidate key selected by the database designer to uniquely identify each row in a table. It is the most important key in the relational model because it is used for indexing, referencing, and maintaining data integrity.
Every table in a relational database is expected to have exactly one primary key, although that key may consist of one attribute or multiple attributes (composite primary key).
A primary key is essentially the chosen unique identifier for the table.
What is a Primary Key?
A primary key is:
A candidate key selected as the main identifier of the table
Used to uniquely identify every row
Mandatory and cannot contain NULL values
Used extensively in indexing and relationships
Referenced by foreign keys in other tables
The DBMS automatically enforces uniqueness and non-null constraints on the primary key.
Properties of a Primary Key
1. Uniqueness
Every row must have a unique primary key value.
No two rows can share the same primary key.
2. Non-Null
Primary key attributes cannot contain NULL values.
Every row must always have a valid identifier.
3. Minimal
The primary key should contain only the attributes necessary for uniqueness.
No unnecessary attribute should be included.
4. Stability (Recommended)
A primary key should ideally never change once assigned.
Changing primary keys may affect related foreign-key references.
5. One Primary Key per Table
A table can have only one primary key.
However, that primary key may contain multiple columns (composite primary key).
Example of a Primary Key
Consider a STUDENT table:
| Roll_No | Name | Branch | CGPA | |
|---|---|---|---|---|
| 101 | aman@email.com | Aman | CSE | 8.5 |
| 102 | riya@email.com | Riya | ECE | 7.9 |
| 103 | kunal@email.com | Kunal | ME | 8.1 |
Here:
Roll_Nouniquely identifies each studentEmailis also unique
Suppose the designer selects:
Roll_No
as the primary key.
Then:
Roll_No must be unique
Roll_No cannot be NULL
other tables can reference Roll_No using foreign keys
SQL Example
Here:
Roll_Nois the primary keyEmailis an alternate key using UNIQUE constraint
Composite Primary Key
A primary key can also contain multiple attributes.
Example:
Here:
the combination of
Roll_NoandCourse_ID
uniquely identifies each rowthis is called a composite primary key
Primary Key vs Candidate Key
| Feature | Candidate Key | Primary Key |
|---|---|---|
| Definition | Any minimal unique identifier | Chosen candidate key |
| Number allowed | Multiple | Only one |
| NULL allowed | No | No |
| Used for main referencing | Not necessarily | Yes |
| Role | Potential identifier | Official identifier |
Every primary key is a candidate key, but not every candidate key becomes the primary key.
Primary Key vs Unique Key
| Feature | Primary Key | Unique Key |
|---|---|---|
| NULL values | Not allowed | Usually allowed (DBMS dependent) |
| Number per table | One | Multiple |
| Main table identifier | Yes | No |
| Used for foreign keys | Commonly | Sometimes |
Why Primary Keys Matter?
Primary keys are extremely important because they:
uniquely identify rows
prevent duplicate records
maintain entity integrity
support relationships between tables
enable efficient indexing and searching
form the basis for foreign keys
Without a primary key, managing updates, deletes, and relationships becomes unreliable.
Choosing a Good Primary Key
A good primary key should be:
unique
short
stable
non-null
simple
unlikely to change
Examples of good primary keys:
Roll_No
Employee_ID
Aadhaar_No
Product_ID
Natural Key vs Surrogate Key as Primary Key
Natural Key
Uses real business data.
Example:
Email
Aadhaar_No
Roll_No
Surrogate Key
Artificial system-generated value.
Example:
Auto-increment ID
UUID
Modern systems often prefer surrogate keys for stability and performance.
Advantages of Primary Keys
Ensures Uniqueness
Prevents duplicate rows.
Improves Search Performance
Indexes are usually created automatically.
Supports Relationships
Foreign keys reference primary keys.
Maintains Integrity
Ensures every row is identifiable.
Problems Without a Primary Key
Without a primary key:
duplicate rows may exist
updates become ambiguous
deletions become risky
relationships become unreliable
data integrity weakens
Summary
A Primary Key in DBMS is the selected candidate key used as the main unique identifier for rows in a table. It must be unique and non-null and is used extensively for indexing, searching, and maintaining relationships between tables. Primary keys form the foundation of relational database design and are essential for enforcing data integrity and reliable table relationships.