Introduction
A primary key is a column (or set of columns) that uniquely identifies every row in a table; it cannot contain NULLs and must be unique across the table. A foreign key is a column in one table that references the primary key of another table, creating a link between the two and enforcing referential integrity — the guarantee that the referenced row actually exists. Together, primary and foreign keys are the backbone of the relational model, allowing data to be split across normalized tables while still being reliably joined back together.
Cricket analogy: A player's unique jersey number acts as a primary key identifying them uniquely in a squad, while a match's 'captain_id' column acts as a foreign key referencing that player, linking match records back to the roster.
Syntax
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
department_id INT,
CONSTRAINT fk_employees_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE SET NULL
ON UPDATE CASCADE
);Explanation
A primary key can be a single column (employee_id) or a composite of multiple columns, common in junction tables (student_id, course_id). Most engines automatically create a unique index on the primary key, which also speeds up lookups and joins. A foreign key does not need to be unique on its own table; many employees can share the same department_id. The ON DELETE and ON UPDATE clauses control what happens to child rows when the referenced parent row is deleted or its key changes: CASCADE propagates the change, SET NULL clears the reference, and RESTRICT (the default in many systems) blocks the operation if dependent rows exist.
Cricket analogy: A junction table linking (player_id, tournament_id) as a composite primary key is like a stats sheet tracking each player's stint per tournament; if a franchise (team_id) is dissolved, ON DELETE CASCADE removes its players' contract rows automatically.
Example
-- Composite primary key example: order_items references two parents
CREATE TABLE order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- Attempting to insert a non-existent product fails:
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 9999, 2); -- ERROR: violates foreign key constraintOutput
Because product_id 9999 does not exist in the products table, the database engine rejects the INSERT with a foreign key violation error rather than silently storing an orphaned reference. If department_id 5 is deleted from departments in the earlier example, ON DELETE SET NULL means every employee row with department_id = 5 automatically becomes NULL instead of the delete being blocked or the employee rows being silently orphaned. This is how referential integrity is enforced automatically by the database rather than relying on application code.
Cricket analogy: Rejecting an INSERT referencing a nonexistent player_id 9999 is like a scorecard system refusing to log a delivery from a player not on the registered squad list, preventing an orphaned, meaningless stat entry.
Key Takeaways
- A primary key uniquely identifies each row and cannot be NULL.
- A foreign key references a primary (or unique) key in another table to enforce valid relationships.
- Composite primary keys combine multiple columns, often used in junction tables.
- ON DELETE/ON UPDATE clauses (CASCADE, SET NULL, RESTRICT) control referential integrity behavior.
Practice what you learned
1. Which of the following is a required property of a primary key?
2. What does a foreign key constraint enforce?
3. What happens with ON DELETE CASCADE when a parent row is deleted?
4. When is a composite primary key typically used?
Was this page helpful?
You May Also Like
Database Design and ER Diagrams
Learn how to model real-world data using entities, attributes, and relationships before writing any SQL.
Constraints and Data Integrity
Explore how PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT constraints enforce valid data.
INNER JOIN
Combine rows from two tables where the join condition matches in both, discarding unmatched rows.