Primary Keys & Foreign Keys

Primary keys

A primary key uniquely identifies each row. It is automatically NOT NULL and UNIQUE, and in InnoDB it also determines the physical order of the data — the clustered index.

-- surrogate key (recommended default)
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

-- natural key
CREATE TABLE countries (code CHAR(2) PRIMARY KEY, name VARCHAR(100));

-- composite key
CREATE TABLE enrollments (
  student_id INT, course_id INT,
  PRIMARY KEY (student_id, course_id)
);

Rules of thumb: keep the primary key small, numeric, immutable and monotonically increasing. Every secondary index in InnoDB stores a copy of the primary key, so a wide key such as a UUID string inflates every index and fragments inserts. If you need UUIDs, store them as BINARY(16) via UUID_TO_BIN(uuid, 1).

Foreign keys

A foreign key makes one table point at another and refuses values that do not exist.

CREATE TABLE enrollments (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT UNSIGNED NOT NULL,
  course_id  INT UNSIGNED NOT NULL,
  CONSTRAINT fk_en_student FOREIGN KEY (student_id)
    REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_en_course FOREIGN KEY (course_id)
    REFERENCES courses(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

Requirements: both tables must be InnoDB, the referenced column must be indexed (a primary or unique key), and the two columns must have the same type, sign and collation.

Referential actions

Action	On delete of the parent row
RESTRICT (default)	Block the delete
NO ACTION	Same as RESTRICT in MySQL
CASCADE	Delete the child rows too
SET NULL	Set the child column to NULL (must be nullable)

CASCADE is convenient for true ownership (order → order items) but dangerous for shared references: deleting one parent can silently erase thousands of rows. Prefer RESTRICT and delete explicitly when you are unsure.

Relationship types

One-to-One    users ── profiles              FK + UNIQUE on the child
One-to-Many   departments ──< students       FK on the "many" side
Many-to-Many  students >──< courses          a junction table with two FKs

-- many-to-many junction
CREATE TABLE student_courses (
  student_id INT UNSIGNED,
  course_id  INT UNSIGNED,
  enrolled_at DATE NOT NULL,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id)  REFERENCES courses(id)  ON DELETE CASCADE
);

Working with foreign keys

-- temporarily disable during a bulk import, then re-enable
SET FOREIGN_KEY_CHECKS = 0;
-- … load data …
SET FOREIGN_KEY_CHECKS = 1;

SELECT table_name, column_name, referenced_table_name
FROM information_schema.key_column_usage
WHERE referenced_table_name IS NOT NULL AND table_schema = DATABASE();

Always turn checks back on, and validate the loaded data afterwards — MySQL does not re-verify existing rows when you re-enable checking.