VMware ESXi and vSphere Cluster Management

Modify Tables with ALTER TABLE in MySQL

Learn how to use MySQL ALTER TABLE to rename tables, modify column types, add columns, and safely drop columns.

ALTER TABLE is the MySQL statement used to change the schema of an existing table. A schema is the structural definition of a database object, including its table name, columns, data types, and related properties.

This lesson uses a simple table named testtable with two rows:

The year column initially uses a character type such as CHAR. We will rename the table, change year to INT, add a postcode column, and then remove it.

ALTER TABLE and data changes

Schema changes affect the structure of a table. For example, they can add a column or change a column's data type.

These operations are different from changing row values:

• SELECT reads rows and values.
• INSERT adds new rows.
• UPDATE changes values in existing rows.
• DELETE removes rows.
• ALTER TABLE changes the table definition.

The main forms used in this lesson are RENAME TO, MODIFY, ADD COLUMN, and DROP COLUMN.

Inspect the table before changing it

Before altering a table, inspect its current structure and data. A query such as the following shows the current rows:

SELECT * FROM testtable;

You should also inspect the column definitions with your MySQL client's table-description command, such as DESCRIBE testtable;. Confirm the exact table name, column names, data types, and constraints before running a schema change.

Rename an existing table

Use RENAME TO to assign a new name to an existing table:

ALTER TABLE old_name RENAME TO new_name;

old_name is the current table name, and new_name is the replacement name. For the example, rename testtable to testtb:

ALTER TABLE testtable RENAME TO testtb;

The table is now called testtb. All later queries and schema changes in this lesson must use testtb, not testtable.

For example:

SELECT * FROM testtb;

Change a column data type with MODIFY

A column's data type determines what kind of values it can store. CHAR is a fixed-length character-string type, while INT stores integer numbers.

Use MODIFY to replace a column definition:

ALTER TABLE table_name MODIFY column_name new_type;

Change the example table's year column from a character-based definition to INT:

ALTER TABLE testtb MODIFY year INT;

Existing values must be compatible with the target type. Values such as 1991 and 1955 can be represented as integers. If the column contains values such as unknown or other non-numeric text, the conversion may fail or produce warnings. Inspect and clean incompatible values before changing the type.

MODIFY supplies the resulting column definition. If the original column has important attributes such as NOT NULL, a default value, or other properties, include the required attributes in the new definition rather than unintentionally replacing them. For example:

ALTER TABLE testtb MODIFY year INT NOT NULL;

Use the version that matches the intended definition of your column.

Add a column

ADD COLUMN creates a new column in an existing table:

ALTER TABLE table_name ADD COLUMN column_name data_type;

Add an integer column named postcode to testtb:

ALTER TABLE testtb ADD COLUMN postcode INT;

Because this new column is nullable and has no default value, existing rows receive NULL. NULL means that the column has no value for that row; it is not the same as zero or an empty string.

Inspect the revised table:

SELECT * FROM testtb;

You can later assign values with UPDATE if the records need postcodes.

Remove a column

DROP COLUMN permanently removes a column's definition and all values stored in that column:

ALTER TABLE table_name DROP COLUMN column_name;

Remove the postcode column from the example table:

ALTER TABLE testtb DROP COLUMN postcode;

Confirm that it is no longer present:

SELECT * FROM testtb;

Complete example sequence

The following statements perform the changes in order. The table is renamed first, so every later statement uses testtb.

ALTER TABLE testtable RENAME TO testtb;

ALTER TABLE testtb MODIFY year INT;

ALTER TABLE testtb ADD COLUMN postcode INT;

SELECT * FROM testtb;

ALTER TABLE testtb DROP COLUMN postcode;

SELECT * FROM testtb;

Understanding MySQL command results

After a successful schema change, MySQL commonly reports feedback such as Query OK. It may also display an affected-record count, a duplicate count, and a warning count.

• Affected records: rows that MySQL changed or rewrote while applying the operation. A schema change can report affected rows even though you did not issue an UPDATE.
• Duplicates: duplicate-related results detected during an operation. Many simple table changes report zero.
• Warnings: notices about conversions or other conditions that did not necessarily stop the statement. Review warnings when changing data types.

A successful message does not remove the need to verify the resulting schema and data with a query or table-description command.

Safe schema-change practices

1. Inspect the current table structure and rows before changing them.
2. Test the operation on a copy of the table or in a development database first.
3. Keep a current backup when the table contains important data.
4. Check whether applications, reports, views, queries, or other references use the table or column being changed.
5. Update dependent SQL and application code after a rename or column change.
6. Double-check every DROP COLUMN statement because it permanently removes stored values.
7. Review warnings after conversions, especially when changing character data to INT.

Adding a nullable column without a default commonly gives existing rows NULL. If every row must have a value, plan the constraint, default, and data-population steps before making the column non-nullable.

Troubleshooting

The table cannot be found after renaming

Commands may still refer to testtable. Replace the old name with testtb in all later SELECT and ALTER TABLE statements.

Changing a column to INT fails or produces warnings

One or more existing values may not be valid integers. Inspect the data, clean incompatible values, run the modification again if appropriate, and review MySQL's warnings.

Existing rows contain NULL after adding a column

This is expected when a nullable column has no default. Assign values with UPDATE later, or plan an appropriate default and NOT NULL strategy before adding the column.

Data is missing after dropping a column

DROP COLUMN removed the column and its stored values. Restore from a backup if one is available. If the data is important, test the statement and verify the target column before running it.

MySQL rejects the ALTER TABLE statement because of permissions

The account may not have the required ALTER privilege. Use an account with suitable permissions or request access from the database administrator.

Operation summary

Key points

• ALTER TABLE changes an existing table's schema.
• Use RENAME TO to rename a table.
• Use MODIFY to change a column definition or data type.
• Use ADD COLUMN to create a column.
• Use DROP COLUMN to permanently remove a column and its values.
• Verify data compatibility, dependencies, backups, and warnings before applying changes.