Primary & Foreign Keys

The previous lesson deliberately stopped short of creating reviews, watchlist, and movie_genres — the three StreamHub tables that connect everything else together. That connection is built entirely out of two concepts: the primary key and the foreign key. Once both click, the rest of this track's schema design falls into place.

Primary Keys: Uniquely Identifying a Row

A primary key is the column (or combination of columns) that uniquely identifies every row in a table — no two rows may ever share the same primary key value, and it can never be left empty. Every table built so far has used the same reliable pattern:

id INT AUTO_INCREMENT PRIMARY KEY

Every table should have a primary key, even a table where nothing else in the row looks like it needs one. Without it, there's no reliable way to say "this exact row" — you'd be stuck matching on a combination of other columns that might coincidentally repeat, or updating and deleting more rows than you meant to. A primary key is also what a foreign key points at, which is the whole subject of the rest of this lesson.

Foreign Keys: Referencing Another Table's Row

A foreign key is a column that stores the primary key value of a row in another table, and MySQL actively enforces that the referenced row actually exists. Here's the reviews table, using exactly that pattern twice:

CREATE TABLE reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  movie_id INT,
  rating INT NOT NULL,
  comment TEXT,
  review_date DATE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (movie_id) REFERENCES movies(id)
);

user_id and movie_id are plain integer columns, but the two FOREIGN KEY clauses tell MySQL exactly what they're allowed to point at: a user_id must match an existing id in users, and a movie_id must match an existing id in movies. This is the enforcement mechanism referenced back in What Is MySQL? — the thing that keeps a review from ever pointing at a user or movie that doesn't actually exist.

What Happens When a Foreign Key Is Violated

Try inserting a review for a user_id that doesn't exist in the users table:

INSERT INTO reviews (user_id, movie_id, rating, review_date)
VALUES (9999, 1, 5, '2026-08-01');

MySQL rejects it outright, with an error along these lines:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`streamhub`.`reviews`, CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))

That error is the foreign key constraint doing exactly its job, not a bug to work around. It's MySQL refusing to create a review that would silently reference a user who was never actually created — precisely the kind of inconsistency a relational database exists to prevent.

Controlling What Happens on Delete

By default, MySQL blocks you from deleting a row that other rows still reference. You can change that behavior explicitly with an ON DELETE clause on the foreign key.

Applying that thinking, the reviews table's movie relationship would be declared like this:

FOREIGN KEY (movie_id) REFERENCES movies(id) ON DELETE CASCADE

while the user relationship is left as RESTRICT (or set up with SET NULL if the column allows nulls) so that removing a user's account is a deliberate decision, not something that quietly wipes out every review they ever wrote as a side effect.

Creating the watchlist Table

watchlist follows the exact same two-foreign-key shape as reviews:

CREATE TABLE watchlist (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  movie_id INT,
  added_date DATE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (movie_id) REFERENCES movies(id) ON DELETE CASCADE
);

Here, cascading on both sides makes sense either way: a watchlist entry has no purpose once either the user or the movie it points to is gone.

Why Many-to-Many Needs a Junction Table

A single foreign key column can only ever point at one row. That's fine for reviews.movie_id, because one review is always about exactly one movie. But the relationship between movies and genres isn't like that at all — a single movie can reasonably belong to several genres at once (a movie can be both "Sci-Fi" and "Comedy"), and a single genre obviously applies to many movies. Neither table can hold a foreign key pointing at the other, because neither side is limited to just one match.

The fix is a dedicated junction table — a table whose entire purpose is recording individual movie-genre pairings, one row per pairing:

CREATE TABLE movie_genres (
  movie_id INT,
  genre_id INT,
  PRIMARY KEY (movie_id, genre_id),
  FOREIGN KEY (movie_id) REFERENCES movies(id) ON DELETE CASCADE,
  FOREIGN KEY (genre_id) REFERENCES genres(id) ON DELETE CASCADE
);

If a movie belongs to two genres, that's simply two rows in movie_genres — one for each pairing. No table needs to store a list inside a single column; the relationship lives entirely as separate rows instead.

The Composite Primary Key

Notice movie_genres has no dedicated id column at all. Instead, PRIMARY KEY (movie_id, genre_id) declares a composite primary key — the combination of both columns together must be unique, even though neither column is unique on its own (the same movie_id legitimately appears in several rows, paired with different genres each time). This specific pairing is the natural, correct choice here for a simple reason: a movie logically can't have the same genre listed against it twice, and the composite key enforces exactly that — attempting to insert the same (movie_id, genre_id) pair a second time is rejected as a duplicate, the same way any other primary key duplicate would be.