MySQL ALTER TABLE: Change an Existing Table Structure

Use MySQL ALTER TABLE to change the structure of an existing table. You can add, modify, rename, or drop columns; change defaults; add or remove indexes; rename the table; and change other table properties.

ALTER TABLE student
ADD COLUMN rank INT NULL;

ALTER TABLE Syntax Top ↑

ALTER TABLE table_name
alter_action;

The action can add, modify, rename, or drop a column or constraint. MySQL also allows several alter actions in one statement.

Add a Column Top ↑

Add a nullable integer column:

ALTER TABLE student
ADD COLUMN rank INT NULL;

Add a required column with a default:

ALTER TABLE student
ADD COLUMN rank INT NOT NULL DEFAULT 10;

Adding NOT NULL to an existing populated table Top ↑

When existing rows already exist, think about what value the new column should contain for those rows. A meaningful DEFAULT, a nullable transition, or a staged migration may be safer than adding a required column without planning the existing data.

FIRST and AFTER Top ↑

MySQL can place a new column at the beginning of the table definition or after another column.

ALTER TABLE student
ADD COLUMN last_name VARCHAR(50) NULL
AFTER name;

To place a column first:

ALTER TABLE student
ADD COLUMN student_code VARCHAR(20) NOT NULL FIRST;

Modify a Column Definition Top ↑

Use MODIFY COLUMN when the column name stays the same but its datatype, NULL rule, default, or other definition changes.

ALTER TABLE student
MODIFY COLUMN last_name VARCHAR(80) NULL;

For example, change mark to an unsigned small integer:

ALTER TABLE student
MODIFY COLUMN mark TINYINT UNSIGNED NOT NULL DEFAULT 0;

Rename a Column Top ↑

On MySQL 8.0+, use RENAME COLUMN when only the name changes:

ALTER TABLE student
RENAME COLUMN mark TO student_mark;

This is clearer than repeating the full datatype definition when only the column name needs to change.

CHANGE COLUMN Top ↑

CHANGE COLUMN can rename a column and redefine it in the same operation. This syntax is also useful on older MySQL versions that do not support RENAME COLUMN.

ALTER TABLE student
CHANGE COLUMN mark student_mark
TINYINT UNSIGNED NOT NULL DEFAULT 0;

Change or Remove a DEFAULT Top ↑

Set a default value:

ALTER TABLE student
ALTER COLUMN mark
SET DEFAULT 0;

Remove the default:

ALTER TABLE student
ALTER COLUMN mark
DROP DEFAULT;

This changes the default for future INSERT operations; it does not rewrite existing row values.

Drop a Column Top ↑

ALTER TABLE student
DROP COLUMN last_name;

Add or Remove a UNIQUE Index Top ↑

Add uniqueness to an existing column:

ALTER TABLE message_table
ADD UNIQUE (msg_id);

This succeeds only if the existing data already satisfies the new uniqueness rule.

To remove that unique index, first identify its index name with SHOW INDEX and then drop it:

SHOW INDEX FROM message_table;

ALTER TABLE message_table
DROP INDEX index_name;

See PRIMARY KEY constraints for the difference between a primary key and other unique keys.

A primary key uniquely identifies each row and cannot contain NULL. A table has one PRIMARY KEY, which may consist of one or more columns.

Primary Key constraint →

Convert a Column to AUTO_INCREMENT Top ↑

An AUTO_INCREMENT column must be indexed, and a table can have only one AUTO_INCREMENT column.

If msg_id already contains unique, non-NULL integer values, you can make it the primary key and add AUTO_INCREMENT:

ALTER TABLE message_table
ADD PRIMARY KEY (msg_id);

ALTER TABLE message_table
MODIFY COLUMN msg_id
INT UNSIGNED NOT NULL AUTO_INCREMENT;

If the table already has a primary key and msg_id should remain a secondary key, a UNIQUE index can satisfy the indexing requirement instead:

ALTER TABLE message_table
ADD UNIQUE (msg_id);

ALTER TABLE message_table
MODIFY COLUMN msg_id
INT UNSIGNED NOT NULL AUTO_INCREMENT;

See MySQL AUTO_INCREMENT.

Rename a Table Top ↑

ALTER TABLE student
RENAME TO students;

Renaming a table can break application code, views, routines, documentation, or integrations that still use the old name. Check dependencies before changing it.

Make Several Changes in One ALTER TABLE Top ↑

MySQL allows multiple alter actions in one statement:

ALTER TABLE student
ADD COLUMN last_name VARCHAR(80) NULL,
MODIFY COLUMN class VARCHAR(20) NOT NULL,
ADD INDEX idx_class (class);

Combining compatible changes can reduce repeated table-alter operations, but the statement becomes more consequential. Test it carefully before production use.

Existing Data and Constraints Top ↑

ALTER TABLE must respect the data already stored in the table.

Adding UNIQUE when duplicates exist Top ↑

This fails if duplicate values are already present:

ALTER TABLE student
ADD UNIQUE (name);

Check duplicates first:

SELECT name,
       COUNT(*) AS row_count
FROM student
GROUP BY name
HAVING COUNT(*) > 1;

Changing a column to NOT NULL Top ↑

Check whether NULL values already exist before making the column required:

SELECT COUNT(*) AS null_rows
FROM student
WHERE last_name IS NULL;

Resolve incompatible existing rows before applying the stricter definition.

Inspect the Current Structure Top ↑

Before altering a table, inspect its current definition.

DESCRIBE student;

or:

SHOW CREATE TABLE student;

SHOW CREATE TABLE is especially useful because it exposes the full stored CREATE definition, including indexes and table options.

Example modern student structure Top ↑

Transactions and DDL Top ↑

Schema-changing statements such as many forms of ALTER TABLE are DDL operations. In MySQL, DDL commonly causes implicit commits and should not be treated like ordinary transactional INSERT/UPDATE/DELETE work.

Performance and Locking Top ↑

• Some ALTER TABLE operations are metadata-only or online; others may rebuild or copy large amounts of table data.
• Large-table changes can consume CPU, disk I/O, temporary space, and time.
• Depending on the change and MySQL version, concurrent reads or writes may be restricted while the operation runs.
• Test important ALTER statements on a copy of production-like data before deployment.
• Schedule potentially expensive structural changes for an appropriate maintenance window.
• Check application queries, foreign keys, indexes, views, routines, and integrations that depend on the changed column or table name.

Common ALTER TABLE Mistakes Top ↑

Using INT(3) as a three-digit limit Top ↑

Integer display width is not a value-length constraint. Use the correct integer type and application validation.

Assuming NOT NULL stores 0 automatically Top ↑

NOT NULL only disallows NULL. If zero should be the default, declare DEFAULT 0 explicitly when that meaning is correct.

Using CHANGE when only MODIFY is needed Top ↑

Use MODIFY when the name remains the same. Use RENAME COLUMN when only the name changes on MySQL 8.0+, and CHANGE when you need rename + definition change or older-version compatibility.

Forgetting the full definition in CHANGE/MODIFY Top ↑

Restate the intended complete column definition so existing attributes are not accidentally lost.

Adding UNIQUE before checking duplicates Top ↑

The ALTER fails if current rows violate the proposed uniqueness constraint.

Dropping a column without checking dependencies Top ↑

Application code, indexes, generated columns, views, or reports may depend on the column.

Assuming ALTER TABLE is always instant Top ↑

Cost depends on the specific change, table size, MySQL version, storage engine, and algorithm chosen by MySQL.

Related Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: