Primary Key vs Foreign Key

Primary keys and foreign keys are how relational databases actually implement the "relational" part — connecting rows across tables while guaranteeing that data stays consistent.

Primary key: uniquely identifies a row

A primary key is a column (or combination of columns) that uniquely identifies every row in a table. It enforces two things automatically: every value must be unique (no two rows share one), and no value can be NULL (every row must have one).

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL
);

A table can only have one primary key, though that key can span multiple columns — a composite primary key, common in join tables (covered in the joins lesson):

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

Here, no single column is unique on its own (a student can enroll in many courses, a course has many students), but the combination of student_id and course_id is guaranteed unique — a student can't enroll in the same course twice.

Foreign key: references another table's primary key

A foreign key is a column that stores a value matching a primary key in another table, establishing a relationship between the two:

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  total DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

orders.user_id refers to users.id. Declaring it as a foreign key isn't just documentation — the database actively enforces referential integrity: it refuses to insert an order with a user_id that doesn't exist in users, and by default refuses to delete a user who still has orders referencing them.

Controlling what happens on delete

ON DELETE decides what the database does when a referenced row is deleted:

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

• ON DELETE CASCADE — deleting the user automatically deletes their orders too.
• ON DELETE SET NULL — the order's user_id is set to NULL instead (only valid if the column allows NULL).
• ON DELETE RESTRICT (the default in most databases) — the delete is blocked entirely while related orders still exist.

Which one is correct depends entirely on what the relationship means — cascading a delete makes sense for "comments belonging to a deleted post," but is usually the wrong choice for "orders belonging to a deleted customer," where you'd typically want to keep historical order records intact.

With keys establishing how tables relate to each other, the next lesson covers JOIN — the tool for actually querying across those relationships at once.