Database Keys: Primary, Foreign, Candidate, and Composite

Introduction

Keys are the fundamental mechanism by which Database Management Systems uniquely identify rows, enforce data integrity, and link related tables together. Every well-designed relational database depends on correctly defined keys. Without proper keys, duplicate data enters the database, foreign key relationships break down, and queries become unreliable.

Explanation

A super key is any set of columns that uniquely identifies a row. From the set of all super keys, the minimal ones (removing any column breaks uniqueness) are called candidate keys. The database designer chooses one candidate key to be the primary key — the official unique identifier for each row. All other candidate keys become alternate keys.

A foreign key is a column (or set of columns) in one table whose values must match values in the primary key of another table. Foreign keys implement referential integrity — the guarantee that relationships between tables remain valid.

Real World Example

In a university database, the students table has student_id as the primary key. The email column is also unique, making it a candidate key. The enrollments table has student_id as a foreign key referencing students.student_id — you cannot enroll a student who does not exist in the students table, and you cannot delete a student who has active enrollments without first handling the enrollments (CASCADE, RESTRICT, or SET NULL).

Technical Breakdown

Key types in detail:

• Primary Key: Uniquely identifies each row. Cannot be NULL. One per table. Can be a single column or a composite of multiple columns.

• Candidate Key: Any minimal super key — a potential primary key. All primary keys are candidate keys, but not all candidate keys become primary keys.

• Foreign Key: References the primary key of another table. Enforces referential integrity. ON DELETE and ON UPDATE clauses define cascade behavior.

• Composite Key: A primary key made of two or more columns. Used when no single column uniquely identifies a row — common in junction tables (e.g., enrollment_id composed of student_id + course_id).

• Surrogate Key: An artificial key generated by the database (auto-increment integer or UUID) with no business meaning. Preferred when natural keys are long, changeable, or complex.

• Natural Key: A key derived from real-world data (e.g., national ID number, ISBN). Risky if the real-world data changes.

Example

-- Surrogate primary key (recommended approach)
CREATE TABLE employees (
    employee_id  INT          PRIMARY KEY AUTO_INCREMENT,
    national_id  VARCHAR(20)  UNIQUE NOT NULL,   -- candidate key (alternate)
    email        VARCHAR(150) UNIQUE NOT NULL,   -- candidate key (alternate)
    first_name   VARCHAR(50)  NOT NULL,
    last_name    VARCHAR(50)  NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- Composite primary key in a junction table
CREATE TABLE student_courses (
    student_id INT,
    course_id  INT,
    grade      DECIMAL(3,1),
    PRIMARY KEY (student_id, course_id),  -- composite PK
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

Common Mistakes

• Using a mutable natural key as a primary key — if an email address is the primary key and users change their email, every foreign key reference across the database must update.

• Allowing NULL in a foreign key column without intent — NULL means "no relationship," which is sometimes valid (optional relationship) but often indicates a design error.

• Forgetting to index foreign keys — unindexed foreign keys cause full table scans on every JOIN operation, dramatically reducing performance.

Interview Tips

Interviewers frequently ask: "What is the difference between a primary key and a unique key?" — a primary key cannot be NULL and there is only one per table; a unique constraint allows NULL (one NULL per column) and multiple unique constraints can exist per table.You should also understand how tables are related using primary keys and foreign keys, and how this relationship is used to retrieve data across multiple tables using JOIN operations. Also be ready to explain ON DELETE CASCADE vs ON DELETE RESTRICT — cascade automatically deletes child rows when a parent is deleted; restrict prevents deletion of a parent with existing children.