ALTER TABLE

Adding Columns

ALTER TABLE ... ADD lets you insert a new column into an existing table without dropping and recreating it, preserving all the data already stored in the other columns.

New columns are typically added as nullable or with a DEFAULT value, since existing rows won't have any value for the newly added column otherwise.

Example: Adding Columns

ALTER TABLE users ADD email VARCHAR(100);

Dropping Columns

DROP COLUMN permanently deletes both the column definition and every value stored in it for every row — there's no partial or reversible version of this operation. Because of this permanence, it's wise to back up a table (or at least that column's data) before running a DROP COLUMN in production.

Example: Dropping Columns

ALTER TABLE users DROP COLUMN email;

Modifying Column Data Types

MODIFY changes an existing column's data type or size, such as widening a VARCHAR(50) to VARCHAR(100) to accommodate longer values without touching the data already stored. Shrinking a column's size can silently truncate existing values that no longer fit, so this should always be checked against current data first.

Example: Modifying Column Data Types

ALTER TABLE users MODIFY name VARCHAR(100);

Renaming Columns

RENAME COLUMN changes only the column's name in the schema; the underlying stored values are completely untouched, so existing data survives the rename unchanged. Any views, stored procedures, or application queries that reference the old column name by string will also need to be updated separately.

Example: Renaming Columns

ALTER TABLE users RENAME COLUMN name TO full_name;

Renaming Tables

RENAME TO changes the table's own name — useful when refactoring a schema, though remember any application code or foreign keys referencing the old name will need updating too. Unlike renaming a column, renaming a table has no effect on the data or column structure inside it whatsoever.

Example: Renaming Tables

ALTER TABLE users RENAME TO customers;