ALTER TABLE in MySQL

Changing a table structure

ALTER TABLE lets you change an existing table's structure without deleting it or losing data. You can add new columns, change a column's type, rename columns, or remove columns. It is a DDL command used whenever your data needs evolve.

Add a column

ALTER TABLE students ADD email VARCHAR(100);

This adds an email column to the students table. Existing rows get NULL in the new column until you fill them. You can add several columns in one command too.

Modify a column

ALTER TABLE students MODIFY marks FLOAT;

MODIFY changes a column's data type — here marks becomes a FLOAT to allow decimals. Ensure the existing values fit the new type, or the change may fail or lose data.

Rename a column

ALTER TABLE students RENAME COLUMN name TO full_name;

RENAME COLUMN changes a column's name while keeping its data and type. The old name name becomes full_name. (On older MySQL, use CHANGE with the type instead.)

Drop a column

ALTER TABLE students DROP COLUMN email;

DROP COLUMN removes a column and all its data permanently, but keeps the rest of the table. This is different from DROP TABLE, which deletes the whole table.

Several changes at once

ALTER TABLE students
    ADD phone VARCHAR(15),
    MODIFY marks FLOAT,
    DROP COLUMN email;

You can combine multiple operations in one ALTER TABLE, separated by commas. This is efficient and keeps related changes together.

Common mistakes

• Modifying to a smaller type. Shrinking a type can truncate or reject existing data.
• Confusing DROP COLUMN and DROP TABLE. One removes a column; the other the whole table.
• Forgetting existing rows get NULL. New columns start as NULL unless given a default.
• Old syntax for rename. On older MySQL use CHANGE old new type instead of RENAME COLUMN.

Summary

• ALTER TABLE changes a table's structure without losing data.
• ADD adds a column; MODIFY changes its type.
• RENAME COLUMN renames; DROP COLUMN removes a column.
• You can combine several changes in one statement.
• DROP COLUMN keeps the table; DROP TABLE deletes everything.

Next, compare removal commands in DROP vs TRUNCATE vs DELETE, or add rules with NOT NULL, UNIQUE, CHECK, DEFAULT.