Modify Tables with ALTER TABLE in MySQL

Learn how to use MySQL ALTER TABLE to rename a table, change a column data type, add a column, remove a column, and verify schema changes.

ALTER TABLE is the MySQL statement used to change the schema of an existing table. A table's schema is its structural definition: its name, columns, data types, and related rules.

This is different from changing individual row values. Use UPDATE to change data stored in rows; use ALTER TABLE to change the table structure itself.

This lesson uses a table named testtable and demonstrates four common schema changes:

Renaming a table with RENAME TO
Changing a column definition with MODIFY
Adding a column with ADD COLUMN
Removing a column with DROP COLUMN

Before continuing, you should understand basic table creation, column names, data types, and SELECT queries. You can also review common MySQL data types.

Sample table before modification

Assume that testtable contains person records. Its year column currently stores years as character values, such as '1991' and '1955'.

name  surname    year
Amy   Goodridge  '1991'
Mark  Smith      '1955'

In this example, the values look numeric, but the column's data type is character-based. The following changes will rename the table, convert year to INT, add an optional postcode, and then remove that postcode.

Rename an existing table

Use ALTER TABLE ... RENAME TO ... to assign a new name to an existing table.

ALTER TABLE old_table_name RENAME TO new_table_name;

For the sample table, the original name is testtable and the replacement name is testtb:

ALTER TABLE testtable RENAME TO testtb;

testtable is the old table name. testtb is the new table name. The table's rows and columns remain in the table, but queries must use the new name after the operation:

SELECT * FROM testtb;

Important: Update application code, reports, scripts, and other queries that refer to testtable. They must refer to testtb after the rename.

Rename troubleshooting

A rename can fail if a table named testtb already exists in the same database. Choose an unused target name or resolve the naming conflict before running the statement.

Change a column data type with MODIFY

A column's data type specifies the kind of values it can store. Examples include character types such as CHAR and numeric types such as INT.

Use MODIFY to replace a column definition:

ALTER TABLE table_name MODIFY column_name new_data_type;

To convert the character-based year column in testtb to an integer column, run:

ALTER TABLE testtb MODIFY year INT;

This changes the definition of year from a character type to INT. Compatible existing values such as '1991' can be converted to integer values such as 1991.

Changing a type does not guarantee that every existing value can be converted safely. Values containing letters, unexpected punctuation, or other invalid numeric content can produce warnings or errors. Inspect and clean incompatible values before modifying the column.

Also remember that MODIFY replaces the column definition. If the original column had properties such as NOT NULL or a DEFAULT, include the required properties in the new definition rather than assuming they are preserved exactly.

Verify the changed definition

Use DESCRIBE to inspect the columns and their types:

DESCRIBE testtb;

You can also use:

SHOW COLUMNS FROM testtb;

Look for year in the result and confirm that its type is INT. MySQL may also report affected rows or warnings after an alteration. Warnings are especially important after a type conversion because they can indicate values that were not converted as expected.

Add a new column

Use ADD COLUMN to create a column in an existing table:

ALTER TABLE table_name ADD COLUMN column_name data_type;

Add an integer postcode column to testtb with:

ALTER TABLE testtb ADD COLUMN postcode INT;

Because this declaration does not specify NOT NULL or an explicit default, the new column is nullable. NULL is a marker meaning that a value is absent or unknown; it is not the same as zero or an empty string.

Existing rows do not have postcode values yet, so they commonly show NULL:

SELECT * FROM testtb;

name  surname    year  postcode
Amy   Goodridge  1991  NULL
Mark  Smith      1955  NULL

New rows can receive a postcode when they are inserted. Existing rows can receive values later with an UPDATE statement. If every row must have a value, plan the column's nullability, default, and data-population steps carefully before adding it.

Remove a column with DROP COLUMN

Use DROP COLUMN to remove a column from a table:

ALTER TABLE table_name DROP COLUMN column_name;

To remove the postcode column added above, run:

ALTER TABLE testtb DROP COLUMN postcode;

Dropping a column removes both its definition and all values stored in it. Afterward, the table displays only the remaining columns:

SELECT * FROM testtb;

name  surname    year
Amy   Goodridge  1991
Mark  Smith      1955

Destructive operation: Check the column name carefully and back up important data before using DROP COLUMN. Data removed by this operation is not restored by simply adding the column again.

Any query or application code that references postcode must also be updated after the column is removed.

ALTER TABLE operations at a glance

Rename: ALTER TABLE old_name RENAME TO new_name; changes the table name while retaining its contents.
Modify: ALTER TABLE table_name MODIFY column_name new_type; changes a column definition, such as its data type.
Add: ALTER TABLE table_name ADD COLUMN column_name data_type; creates a new column. Existing rows receive a value based on nullability and default rules.
Drop: ALTER TABLE table_name DROP COLUMN column_name; permanently removes a column and its stored values.

Verify structural and data changes

Use DESCRIBE or SHOW COLUMNS when you need to verify the structure:

DESCRIBE testtb;

Use SELECT when you need to verify which columns and values are visible in the rows:

SELECT * FROM testtb;

After adding a column, SELECT confirms that the column exists and shows values such as NULL for existing records. After dropping a column, it confirms that the column and its values are no longer returned.

For a type change, combine both checks: use DESCRIBE to confirm the definition and SELECT to inspect the stored values.

Troubleshooting ALTER TABLE changes

Target table name already exists

If renaming fails because the new name is already in use, select an unused name or resolve the conflict first. MySQL cannot give two tables in the same database the same name.

Character-to-integer conversion fails or warns

Check the existing values in the column. Non-numeric values may not convert safely to INT. Clean or replace those values, run the modification again, and inspect the warnings reported by MySQL.

New rows show NULL in the added column

This is expected when a nullable column is added without a populated default. Update existing rows if the application requires postcode values, or define an appropriate default and nullability rule when adding the column.

Data is missing after dropping a column

The values were removed because DROP COLUMN is destructive. Restore them from a backup if one is available. In future, verify the table and column name and back up important data before executing the statement.

Applications fail after a schema change

Search dependent SQL, reports, and application code for the old table name or removed column name. Update those references and test affected operations after the schema change.

Key points

ALTER TABLE changes an existing table's schema, not individual row values.
RENAME TO changes the table name, so later queries must use the replacement name.
MODIFY replaces a column definition and requires compatible existing data.
ADD COLUMN adds a new field; existing rows commonly receive NULL when the new nullable column has no explicit default.
DROP COLUMN removes the column definition and every value stored in that column.
Use DESCRIBE or SHOW COLUMNS to verify structure and SELECT to verify visible row data.