Introduction
Key: attribute(s) uniquely identifying row. Essential: relational model. Primary key: designated unique identifier. Candidate key: alternative unique identifier. Foundation: referential integrity, relationships.
"Keys are the glue of relational databases: uniquely identify rows, enable joins, enforce consistency. Careful choice: foundation of good design." -- Relational model
Primary Key
Definition
Chosen candidate key: designated unique identifier. One per table. Not NULL: always. Indexed: automatically. Used: foreign key references.
Properties
Uniqueness: guaranteed. NOT NULL: required. Immutability: should not change (stable). Minimal: no unnecessary columns.
Example
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10, 2)
);Usage
Identify rows: in queries, updates, deletes. Foreign keys: reference by primary key. Relationships: primary key joins tables.
Clustered Index
Physical ordering: by primary key (typically). Performance: ranges on PK fast. Design: consider clustering carefully.
Candidate Keys
Definition
Minimal superkey: uniquely identifies, no redundancy. Multiple per table possible. One becomes primary. Others: alternate unique identifiers.
Example
Employee table candidates:
{employee_id} - numeric identifier (simple)
{ssn} - social security number (natural)
{first_name, last_name, hire_date} - composite (complex)
Choose: employee_id as primary (simple, stable)Non-Primary Candidates
Can define: UNIQUE constraint. Or: UNIQUE index. Enforced: database ensures uniqueness. Used: for lookups.
Selection Criteria
Simplicity: fewer columns. Stability: immutable. Efficiency: indexed, used frequently. Business logic: natural candidates preferred (when applicable).
Superkeys
Definition
Set of attributes uniquely identifying row. May be redundant: contains more columns than necessary. Example: {employee_id, ssn} (both alone are unique, combined redundant).
Relationship to Keys
Superkey: general concept. Candidate key: minimal superkey (no redundancy). Primary key: chosen candidate.
Redundancy
Superkey may be: unnecessary columns. Design: minimize (choose candidate key). Efficiency: fewer columns faster.
Natural Keys
Definition
Business meaning: real-world identifier. Example: ISBN for books, SSN for people, VIN for vehicles. Stable: subject to change (rare).
Advantages
Meaningful: understandable (not arbitrary ID). Existing: often already unique. Business logic: aligned with domain.
Disadvantages
Stability: may change (SSN, email). Size: possibly large. Multi-column: composite keys more complex.
Risk
Change required: expensive (cascades via foreign keys). Solution: combine with surrogate (use both).
Surrogate Keys
Definition
Artificial identifier: created for uniqueness. Example: auto-increment ID, UUID. No business meaning: purely technical.
Advantages
Simplicity: single column. Stability: never changes. Efficiency: small (fast foreign keys). Flexibility: add real key constraints separately.
Disadvantages
Meaningless: arbitrary numbers. Overhead: extra column (small). Hiding relationships: real identity obscured.
Practical
Common: most modern systems use. Combined: surrogate as primary, natural as UNIQUE constraint. Balance: simplicity + business logic.
Auto-Increment
CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
ssn VARCHAR(20) UNIQUE, -- natural key (unique but not primary)
name VARCHAR(100)
);Composite Keys
Definition
Multiple columns: together uniquely identify. Example: {student_id, course_id, semester} uniquely identifies enrollment.
Example
CREATE TABLE enrollments (
student_id INT,
course_id INT,
semester VARCHAR(20),
grade CHAR(1),
PRIMARY KEY (student_id, course_id, semester)
);Usage
Junction tables: many-to-many relationships. Domain entities: natural composite keys. Complex domain: multiple attributes needed.
Foreign Keys
Reference: all columns of composite. Example: FOREIGN KEY (student_id, course_id, semester). Complex: more joins needed.
Considerations
Complexity: more columns, more overhead. Queries: need all columns in WHERE. Normalization: check 2NF (partial dependencies?).
Uniqueness Enforcement
Primary Key Constraint
Unique: guaranteed (database enforces). Index: created automatically. Performance: fast lookup.
UNIQUE Constraint
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE NOT NULL,
username VARCHAR(50) UNIQUE NOT NULL
);UNIQUE INDEX
Alternative: explicit index. Functionally same: ensures uniqueness. More control: index options.
NULL Handling
UNIQUE allows: multiple NULLs (NULL != NULL). Constraint: not enforced for NULLs (varies by DBMS). NOT NULL: required for strict uniqueness.
Violation Handling
Duplicate attempt: error (INSERT/UPDATE fails). Application: must handle (catch exception, retry).
Key Selection Strategies
Decision Process
1. Identify candidates: what uniquely identifies? 2. Choose primary: simplicity, stability, efficiency. 3. Enforce: constraints, indexes. 4. Document: schema comments.
Surrogate vs. Natural
Surrogate: preferred (simple, stable). Natural: add as UNIQUE (preserve business logic). Hybrid: best practice.
Composite vs. Simple
Simple: prefer (faster, simpler joins). Composite: when necessary (junction tables, natural compound identifiers). Evaluate: trade-offs.
Examples by Domain
User: surrogate ID (primary) + email UNIQUE (natural). Product: SKU (natural) + product_id (surrogate). Order: surrogate (order_id) primary.
Practical Examples
Simple Primary Key
CREATE TABLE departments (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50) UNIQUE NOT NULL
);Natural Key
CREATE TABLE books (
isbn VARCHAR(20) PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(100)
);Composite Key
CREATE TABLE enrollments (
student_id INT NOT NULL,
course_id INT NOT NULL,
semester VARCHAR(20) NOT NULL,
grade CHAR(1),
PRIMARY KEY (student_id, course_id, semester),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);Hybrid Approach
CREATE TABLE users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(100) UNIQUE NOT NULL,
username VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(100)
);References
- Ramakrishnan, R., and Gehrke, J. "Database Management Systems." McGraw-Hill, 3rd edition, 2003.
- Silberschatz, A., Korth, H. F., and Sudarshan, S. "Database System Concepts." McGraw-Hill, 6th edition, 2010.