PRIMARY KEY and FOREIGN KEY

Concept

A primary key identifies each row uniquely within its table. A foreign key says that a column's values must exist as a primary key in another table. Together they turn a set of tables into a connected model.

Syntax

-- Primary key: single column
CONSTRAINT pk_departments PRIMARY KEY (id)

-- Primary key: composite
CONSTRAINT pk_assignments PRIMARY KEY (employee_id, project_id)

-- Foreign key
CONSTRAINT fk_employees_dept FOREIGN KEY (dept_id)
    REFERENCES departments(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE

Example

CREATE TABLE departments (
    id   INT PRIMARY KEY,
    name VARCHAR(60) NOT NULL
);

CREATE TABLE employees (
    id      INT PRIMARY KEY,
    name    VARCHAR(60) NOT NULL,
    dept_id INT NULL,
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- Rejected: department 99 does not exist
INSERT INTO employees VALUES (1, 'Asha', 99);

-- Accepted: NULL means "no department yet", which is not a broken reference
INSERT INTO employees VALUES (2, 'Nikhil', NULL);

Explanation

The second insert is the detail people miss: a foreign key constrains non NULL values only. NULL means "no reference", and a missing reference cannot be dangling. If every employee must have a department, the column needs NOT NULL as well as the foreign key.

Primary key properties

• Unique across the table.
• Implicitly NOT NULL - you never declare it.
• One per table, though it may span several columns.
• Backed by an index, which is what makes lookups by key fast.
• In InnoDB it is the clustered key: the table rows are physically stored in primary key order, and every secondary index stores the primary key. A wide primary key therefore makes every index bigger.

Composite primary keys

CREATE TABLE assignments (
    employee_id INT,
    project_id  INT,
    hours       DECIMAL(6,1) NOT NULL DEFAULT 0,
    PRIMARY KEY (employee_id, project_id),
    CONSTRAINT fk_asg_emp  FOREIGN KEY (employee_id) REFERENCES employees(id),
    CONSTRAINT fk_asg_proj FOREIGN KEY (project_id)  REFERENCES projects(id)
);

The composite key does real work here: it enforces that one employee can be assigned to one project only once, which is exactly the business rule for a junction table.

Important rules

• A foreign key must reference a primary key or a unique key - not an arbitrary column.
• The referenced and referencing columns must have compatible types. An INT referencing a BIGINT will be rejected or silently prevent index use.
• Parent rows must exist before child rows. Load and drop tables in dependency order.
• MySQL and MariaDB create an index on the foreign key column automatically. PostgreSQL, SQL Server and Oracle do not - and an unindexed foreign key makes joins and parent deletes slow.
• Foreign keys are only enforced by InnoDB in MySQL. MyISAM parses and ignores them. SQLite needs PRAGMA foreign_keys = ON per connection.

Common mistakes

• Assuming a foreign key stops NULL. It does not; add NOT NULL.
• Forgetting to index the foreign key column outside MySQL.
• Mismatched types between parent and child columns.
• Using a mutable business value - an email, a phone number - as the primary key, and then having to update it everywhere.
• Dropping foreign keys "for performance" and discovering orphan rows months later.

Best practices

• Give every table a primary key. A table without one cannot be reliably updated, replicated or deduplicated.
• Prefer a short, stable surrogate key - an auto increment or identity integer - over a wide natural key.
• Index every foreign key column.
• Name keys consistently: pk_table, fk_child_parent.
• Declare foreign keys even in systems that "handle it in code" - they cost little and catch what code misses.

Practice

1. Add a foreign key from orders.customer_id to customers.id and test it with a bad insert.
2. Explain why assignments uses a composite primary key rather than its own id column.
3. What breaks if employees.dept_id is INT and departments.id is BIGINT?