Summary: in this tutorial, you will learn about the MySQL ALTER TABLE statement that changes existing table structure such as adding or removing column, changing column attribute, etc.
MySQL ALTER TABLE syntax
The ALTER TABLE statement is used to change the structure of existing tables. You can use the ALTER TABLE statement to add or drop column, change data type of column, add primary key, rename table and many more. The following illustrates the ALTER TABLE statement syntax:
ALTER TABLE table_name action1[,action2,…]
To alter an existing table:
First, you specify the table name that you want to change after the
ALTER TABLEkeywords.Second, you list a set of actions that you want to apply to the table. An action can be anything such as adding a new column, adding primary key, renaming table, etc. The
ALTER TABLEstatement allows you to apply multiple actions in a singleALTER TABLEstatement, each action is separated by a comma (,).
Let’s create a new table for practicing the ALTER TABLE statement.
We’re going to create a new table named tasks in our sample database. The following is the script for creating the tasks table.
CREATE TABLE tasks ( task_id INT NOT NULL , subject VARCHAR(45) NULL , start_date DATE NULL , end_date DATET NULL , description VARCHAR(200) NULL , PRIMARY KEY (task_id) , UNIQUE INDEX task_id_UNIQUE (task_id ASC) );
Changing columns using MySQL ALTER TABLE statement
Using MySQL ALTER TABLE statement to set auto-increment attribute for a column
Suppose you want the value of the task_id column to be increased automatically by one whenever you insert a new record into the tasks table. To do this, you use the ALTER TABLE statement to set the attribute of the task_id column to AUTO_INCREMENT as follows:
ALTER TABLE tasks CHANGE COLUMN task_id task_id INT(11) NOT NULL AUTO_INCREMENT;
You can verify the change by adding some records to the tasks table.
INSERT INTO tasks(subject, start_date, end_date, description) VALUES('Learn MySQL ALTER TABLE', Now(), Now(), 'Practicing MySQL ALTER TABLE statement'); INSERT INTO tasks(subject, start_date, end_date, description) VALUES('Learn MySQL CREATE TABLE', Now(), Now(), 'Practicing MySQL CREATE TABLE statement');And you can query data to see if the value of the task_id column is increased by 1 each time you insert a new record:
SELECT task_id, description FROM tasks
Using MySQL ALTER TABLE statement to add a new column into a table
Because of the new business requirement, you need to add a new column called complete to store the percentage of completion for each task in the tasks table. In this case, you can use the ALTER TABLE to add a new column to the tasks table as follows:
ALTER TABLE tasks ADD COLUMN complete DECIMAL(2,1) NULL AFTER description;
Using MySQL ALTER TABLE to drop a column from a table
Suppose you don’t want to store the description of tasks in the tasks table and you have to remove it. The following statement allows you to remove the description column of the tasks table:
ALTER TABLE tasks DROP COLUMN description;
Renaming table using MySQL ALTER TABLE statement
You can use the ALTER TABLE statement to rename a table. Notice that before renaming a table, you should take a serious consideration to see if the change affects both database and application layers.
The following statement rename the tasks table to work_items:
ALTER TABLE tasks RENAME TO work_items;
In this tutorial, you’ve learned how to use the MySQL ALTER TABLE statement to change existing table structure and to rename the table.