Creating Tables

With streamhub created and its columns' data types chosen, it's time to actually build tables inside it. This lesson creates the three simplest StreamHub tables — genres, movies, and users — and shows how to double-check exactly what MySQL created afterward.

The CREATE TABLE Statement

The simplest table in the whole schema is genres — just an id and a name — which makes it the clearest place to see the basic syntax:

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

Each column gets a name, a data type, and optionally a rule like NOT NULL (rejecting any row that leaves this column empty). Genre names like "Drama" or "Sci-Fi" are short and always required, which is exactly what VARCHAR(100) NOT NULL expresses.

What AUTO_INCREMENT Actually Does

AUTO_INCREMENT tells MySQL to assign the next available whole number to that column automatically every time a row is inserted, starting at 1 and counting up. Insert three genres and, without you specifying an id at all, they'll land as 1, 2, and 3. You never have to track "what's the next available id" yourself or worry about two inserts accidentally colliding on the same id — MySQL guarantees each one is unique and increasing on its own.

Creating the movies Table

movies pulls together several of the data types covered in Data Types in one statement:

CREATE TABLE movies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  release_year INT,
  duration_minutes INT
);

title is a VARCHAR(255) since movie titles are short but variable in length, while release_year and duration_minutes are plain INT columns — whole numbers with no fractional component to worry about, matching what was decided in the previous lesson.

Creating the users Table

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(255) NOT NULL,
  signup_date DATE
);

username gets a modest VARCHAR(50) cap, email a more generous VARCHAR(255) since email addresses can legitimately run long, and signup_date a plain DATE since time-of-day precision was never needed for it.

Verifying a Table's Structure

Once a table exists, confirm its exact structure with DESCRIBE:

DESCRIBE users;

SHOW COLUMNS FROM users; is an equivalent statement that returns the exact same information — DESCRIBE is simply the shorter, more commonly typed form of it.

Listing Every Table

To see every table that exists in the currently selected database:

SHOW TABLES;

Seeing the Exact CREATE Statement MySQL Stored

SHOW CREATE TABLE is a genuinely useful sanity check — it returns the exact statement MySQL actually used to build the table, including any defaults or settings it applied silently that you didn't explicitly type:

SHOW CREATE TABLE movies;

Notice MySQL filled in details we never typed — DEFAULT NULL on the nullable columns, and a storage engine (InnoDB, MySQL's default) and character set. This is exactly why SHOW CREATE TABLE is worth reaching for whenever a table isn't behaving the way you expect: it shows you the truth of what got created, not just what you intended to type.

What's Left: The Relationship Tables

reviews, watchlist, and movie_genres — the three tables that connect users, movies, and genres to each other — are intentionally left out of this lesson. All three depend on primary and foreign keys to make any sense, so they're covered properly, together, in the next lesson.