Altering & Dropping Tables

Tables in a real project are rarely finished after the first CREATE TABLE. Product requirements shift, mistakes in the original design surface once real data is in there, and sometimes an entire table needs to disappear. This lesson covers the full ALTER TABLE toolkit for reshaping an existing StreamHub table, and the three very different ways MySQL gives you to remove rows or tables entirely.

Adding a Column

Suppose StreamHub decides every movie needs a poster image. The movies table already exists and already has rows, so we add the column with ALTER TABLE ... ADD COLUMN:

ALTER TABLE movies
ADD COLUMN poster_url VARCHAR(500);

Existing rows get NULL in the new column until you fill them in, and every new insert can now supply a poster URL alongside the movie's other details.

Modifying an Existing Column

A very true-to-life scenario: movies.title was originally defined as VARCHAR(100), but StreamHub's catalog grows to include movies with unusually long titles that keep getting truncated. ALTER TABLE ... MODIFY COLUMN changes the column's definition in place:

ALTER TABLE movies
MODIFY COLUMN title VARCHAR(200) NOT NULL;

MODIFY COLUMN can widen a type, tighten or loosen a constraint, or change a default - anything about the column except its name. The existing data in the column is kept; MySQL only rejects the change if the current data can't be represented in the new definition (for example, shrinking a column so an existing value no longer fits).

Dropping a Column

Removing a column is just as direct, but it comes with a serious caution: every value that column held, for every row, is gone the moment this runs.

ALTER TABLE movies
DROP COLUMN poster_url;

Renaming a Column

MySQL 8.0 added a clean, purpose-built syntax for renaming a column without touching its type:

ALTER TABLE movies
RENAME COLUMN duration_minutes TO runtime_minutes;

Older MySQL versions (and some tools that still favor the classic syntax) use CHANGE COLUMN instead, which renames a column and requires you to restate its full definition in the same statement:

ALTER TABLE movies
CHANGE COLUMN duration_minutes runtime_minutes INT;

The difference is small but easy to trip over: forget to restate the column's type and constraints with CHANGE COLUMN and you can accidentally lose them, since the statement redefines the column from scratch under its new name. RENAME COLUMN avoids that risk entirely by only touching the name.

Renaming an Entire Table

Either of these does the same thing - renaming the whole movies table:

ALTER TABLE movies RENAME TO films;

-- or, as a standalone statement
RENAME TABLE movies TO films;

RENAME TABLE also accepts multiple renames in one atomic statement, which is handy when you need to swap two table names around without a moment where either name is missing.

Three Ways to Remove Rows or Tables

MySQL gives you three genuinely different tools for getting rid of data, and mixing them up can be costly. Here's how DELETE, TRUNCATE TABLE, and DROP TABLE compare when aimed at StreamHub's movies table:

Reach for each one for a different reason:

• DELETE when you need to remove specific rows and want the safety net of a transaction while you're at it.
• TRUNCATE TABLE when you want to empty an entire table quickly - for example, wiping out test data - and don't need any filtering.
• DROP TABLE only when the table itself should stop existing, not just its rows - for example, retiring a feature that no longer needs its own table.

-- Remove one specific movie
DELETE FROM movies WHERE id = 5;

-- Empty the whole catalog table, keep its structure
TRUNCATE TABLE movies;

-- Remove the table entirely, structure and all
DROP TABLE movies;