Using MySQL ALTER TABLE To Change Table Structure?

Summary: in this tutorial, you will learn about the MySQL ALTER TABLE statement that changes existing table structure such as adding or removing columns, changing column attributes, etc.

Introduction to MySQL ALTER TABLE statement

You use the ALTER TABLE  statement to change the structure of existing tables. The ALTER TABLE statement allows you to add a column, drop a column, change the data type of column, add primary key, rename table, and many more. The following illustrates the ALTER TABLE  statement syntax:

1	ALTER TABLE table_name action1[,action2,…]

To change the structure an existing table:

• First, you specify the table name, which you want to change, after the ALTER TABLE  clause.
• 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 TABLE  statement allows you to apply multiple actions in a single ALTER TABLE statement, 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 the sample database. The following is the script for creating the tasks  table.

1 2 3 4 5 6 7 8 9

CREATE TABLE tasks (     task_id INT NOT NULL,     subject VARCHAR(45) NULL,     start_date DATE NULL,     end_date DATE 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 row 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:

1 2	ALTER TABLE tasks CHANGE COLUMN task_id task_id INT(11) NOT NULL AUTO_INCREMENT;

You can verify the change by inserting some rows to the tasks  table.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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 row:

1 2 3 4

SELECT     task_id, description FROM     tasks;

Using MySQL ALTER TABLE statement to add a new column into a table

Because of the new business requirements, 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:

1 2 3

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:

1 2	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 understand if the change impacts both database and application layers.

The following statement renames the  tasks  table to the work_items table:

1 2	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.