ALTER TABLE
In this page:
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;Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: