intro
Let’s learn everything you need to know about the powerful and popular MySQL ALTER TABLE statement to modify tables, columns, indexes, and constraints like a pro.
Databases represent data related to the real world, and just like reality evolves, data in databases needs to change over time, too. That is why one of the cornerstones of SQL is ALTER TABLE—a command used to modify table structure in your database.
In this blog, you will learn how to alter a table in MySQL, exploring what this operation is and how to use it in real-world scenarios.
Let’s dive in!
What Is the MySQL ALTER TABLE Statement?
In MySQL, the ALTER TABLE statement allows you to modify the structure of an existing table. In details, it can be used to add, delete, or drop columns, change column data types, rename columns or tables, add or drop indexes and constraints, and more.
The ALTER TABLE statement is part of the ANSI/ISO SQL standard, so its syntax and capabilities are largely consistent across most popular database systems. However, MySQL offers some specific features. Read on to find out more!
ALTER TABLE MySQL Statement: Syntax and Main Aspects
The syntax of the ALTER TABLE MySQL statement is as follows:
1
ALTER TABLE table_name
2
[alter_option_1 [, ..., alter_option_n]]
3
[partition_options]
Where the allowed alter options include:
Similarly, there are a couple of options exclusive to partitioning and these include:
For more information, see the documentation for partition operations.
ALTER TABLE in MySQL also supports the optional ALGORITHM setting. This enables you to specify how MySQL should perform the table alteration. The syntax is as follows:
1
ALTER TABLE table_name
2
ALGORITHM = {INPLACE | COPY | INSTANT | DEFAULT}
3
[alter_option_1 [, ..., alter_option_n]]
There are a couple of algorithms that are defined in the ALGORITHM parameter and these are as follows:
Note that the ALGORITHM clause is optional. If omitted, MySQL 8.0+ uses ALGORITHM=INSTANT for storage engines and ALTER TABLE clauses that support it. Since there is no support for the ALGORITHM clause in MySQL 8.0, older versions of MySQL do not have a default value.
Main Aspects
I’ve outlined five main aspects you need to be aware of when using the MySQL ALTER TABLE statement and these are as follows:
Use Cases of ALTER TABLE in MYSQL
Now that you know how to use ALTER TABLE in MySQL, you are ready to see it in action in several real-world examples!
Adding a Column
This is an example of a MySQL ALTER TABLE ADD COLUMN query:
1
ALTER TABLE employees
2
ADD COLUMN birthdate DATE;
The query adds a new column named birthdate of type DATE to the employees table. See our guide on the SQL DATE data types.
For more guidance, see our guide about ALTER TABLE ADD COLUMN in SQL.
Adding an Index
1
ALTER TABLE employees
2
ADD INDEX idx_last_name (last_name);
This adds an index named idx_last_name to the last_name column in the employees table, which can help speed up queries that search by last name.
Find out how to get 10x query performance with a database index.
Adding a Key
1
ALTER TABLE employees
2
ADD PRIMARY KEY (id);
The above query sets the id column as the primary key for the employees table, ensuring that each id value is unique.
Adding a Constraint
Below is an example of the MySQL ALTER TABLE ADD CONSTRAINT query:
1
ALTER TABLE employees
2
ADD CONSTRAINT chk_age
3
CHECK (age >= 18);
The query adds a CHECK constraint named chk_age to the employees table. This ensures that the age column only contains values of 18 or greater, enforcing a minimum age requirement.
Modifying a Column
ALTER TABLE MODIFY COLUMN in MySQL adjusts the data type or attributes of an existing column. The syntax to use it is as follows:
1
ALTER TABLE table_name MODIFY COLUMN column_name new_data_type [attributes];
For example, launch the following query to change a column price to DECIMAL in the products table:
1
ALTER TABLE products MODIFY COLUMN price DECIMAL(10, 2);
Dropping a Column
1
ALTER TABLE employees
2
DROP COLUMN birthdate;
The above sample query removes the birthdate column from the employees table.
Dropping a Constraint
Here is an example of a MySQL ALTER TABLE MySQL query that drops a constraint:
1
ALTER TABLE employees
2
DROP CONSTRAINT chk_age;
This removes the CHECK constraint named chk_age from the employees table, allowing age values outside the previously restricted range.
Dropping an Index
1
ALTER TABLE employees
2
DROP INDEX idx_last_name;
That drops the index named idx_last_name from the employees table, which could slow down queries that previously used this index. At the same time, the operation will save disk space.
Dropping a Foreign Key
This is how you can use ALTER TABLE in MySQL to drop a foreign key:
1
ALTER TABLE employees
2
DROP FOREIGN KEY fk_department_id;
The query removes the foreign key named fk_department_id from the employees table, allowing department_id values that do not necessarily match those in the departments table. Discover more in our primary key vs foreign key comparison guide.
Dropping a Primary Key
Similarly, you can drop the primary key from a table by using a query like so:
1
ALTER TABLE employees
2
DROP PRIMARY KEY;
Note that you do not have to specify the primary key name as a table can only have a single primary key.
Renaming a Table
The following query renames the employees table to staff:
1
ALTER TABLE employees
2
RENAME TO staff;
Keep in mind that this operation only changes the table name, preserving its structure and data.
Renaming a Column
1
ALTER TABLE employees
2
RENAME COLUMN last_name TO surname;
This renames the last_name column to surname in the employees table.
A Visual Way to Alter a Table in MySQL
As you have just seen, each ALTER TABLE operation has its own syntax, which can be challenging to remember. The problem is that running an incorrect ALTER TABLE query can have serious consequences for your data. For launching these critical operations, you should use a powerful database client such as DbVisualizer!
DbVisualizer is a feature-rich database client with top user satisfaction ratings. It supports over fifty databases and allows you to visually alter tables.
For example, assume you want to add a new hire_date column to your company database. You can achieve that with these simple steps:
In the dialog window that opens, you can visually create, delete, and modify columns, keys, indexes, and check constraints:
Alternatively, you can copy the query generated by DbVisualizer and run it yourself in the SQL Editor or modify it to suit your needs:
1
ALTER TABLE
2
`company`.`employees` ADD (hire_date DATE)
There you have it! You’ve just learned how to alter a table in MySQL using DbVisualizer.
Conclusion
In this guide, you learned lots about the MySQL ALTER TABLE statement and how it works. You now know the most useful use cases for this popular command, including adding and dropping columns, indexes, and constraints.
Each ALTER TABLE operation requires its own specific syntax, which can be complex to remember. Using the command line to execute such important queries may not be the best approach. To ensure that you make the correct modifications, you must inspect the results of your ALTER TABLE queries. This is where a visual database client like DbVisualizer becomes invaluable!
DbVisualizer is a powerful database client that supports multiple DBMS technologies, allowing you to perform ALTER TABLE operations visually with just a few clicks. On top of that, it comes equipped with advanced features like query optimization, SQL formatting, and ERD-like schema generation. Try DbVisualizer for free today!
FAQ
What does it mean to alter a table in MySQL?
In MySQL, "altering a table" means modifying its structure. That includes adding, deleting, or modifying columns, changing data types, adding indexes, or renaming the table itself. The operation is called “alter” as it is performed via the SQL ALTER TABLE statement.
Is it possible to alter the index structure in MySQL?
No, not directly. To modify an existing index or constraint in MySQL, you must first drop the existing one using DROP INDEX or DROP CONSTRAINT, and then recreate it with the desired modifications using ADD INDEX or ADD CONSTRAINT. However, it is possible to perform an ALTER TABLE query on a column that is part of an index and change its type to make MySQL fully rebuild the index related to it.
How to use ALTER TABLE to change column type in MySQL?
To change a column's data type in MySQL, use the ALTER TABLE statement with MODIFY COLUMN:
1
ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;
For example, you can use to change the salary column to FLOAT in the employees table as below:
1
ALTER TABLE employees MODIFY COLUMN salary FLOAT;
How to alter a table in MySQL without locking the entire table?
Before MySQL 5.6, altering a table in MySQL used to lock the entire table, preventing reads and writes during the operation. MySQL 5.6 introduced the Online DDL feature—also available in InnoDB—that allows you to avoid locks on various ALTER TABLE operations, including adding a column. That behavior should happen automatically, but you can force it by adding ALGORITHM=INPLACE and LOCK=none to your ALTER TABLE statement.
Is it possible to add or drop a column instantly in MySQL/MariaDB?
Yes, starting from MySQL 8.0 and MariaDB 10.3.2, adding or dropping a column can be done instantly for InnoDB tables. That is possible thanks to the INSTANT algorithm, which is used by default in InnoDB. In particular, ALTER TABLE MySQL operations that support the INSTANT algorithm include:
To learn about the restrictions for when you cannot use the INSTANT algorithm, check out the documentation for MySQL and MariaDB.
Why does my MySQL ALTER TABLE ADD COLUMN query take a lot of time?
A MySQL ALTER TABLE ADD COLUMN query can take a long time due to several factors, including
