| Feature | Primary Key | Secondary Key |
|---|
| Definition | A unique identifier for each record in a table | A non-unique key used for searching or filtering data |
| Uniqueness | Must be unique for every record | Can be non-unique |
| Null Values | Not allowed (must always have a value) | Allowed (can contain nulls depending on use) |
| Indexing | Creates a unique index: clustered in InnoDB/SQL Server, plain B-tree on a heap in Postgres | Creates a non-clustered index |
| Purpose | Ensures entity integrity | Used to optimize queries and access patterns |
| Can be Composite? | Yes, can be made of multiple columns | Yes, can also be composite |
| Example Use Case | id in a Users table (a unique email is a candidate/alternate key: enforce with UNIQUE) | department_id in an Employees table used to filter employees by department |
| Existence | Mandatory for every table (usually one per table) | Optional (can have multiple secondary keys in one table) |
| Modifiability | Typically not changed after creation | Can be added or modified based on query requirements |
Example
Table:
Employees
- Primary Key: emp_id → uniquely identifies each employee.
- Secondary Key: department_id → used to filter or search for employees by department.
Summary
- Use a Primary Key to uniquely identify records and ensure data integrity.
- Use Secondary Keys (or secondary indexes) to improve performance on read queries, filters, and lookups that don’t involve the primary key.