Summary: in this tutorial, you will learn how to use MySQL ALTER TABLE statement to change the structure of existing tables.

MySQL ALTER TABLE syntax

MySQL ALTER TABLE statement is used to change the structure of existing tables. You can use MySQL ALTER TABLE to add or drop column, change data type of column, add primary key, rename table and many more. The following illustrates the MySQL ALTER TABLE syntax:

1
ALTER TABLE table_name action1[,action2,…]

First, you specify the table name that you want to change after the ALTER TABLE clause.

Next, 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. MySQL ALTER TABLE statement allows you to apply multiple actions using a single ALTER TABLE statement, each action is separated by a comma.

Let’s create a new table for practicing MySQL ALTER TABLE statement. We’re going to create a new table named tasks in our sample database. Here is the script for creating tasks table.

1
2
3
4
5
6
7
8
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) );

Tasks-Table

Changing columns using MySQL ALTER TABLE statement

Using MySQL ALTER TABLE statement to set auto-increment attribute for a column

Suppose we want the value of the task_id column to be increased automatically by one whenever you insert a new task into the tasks table. In order to accomplish this, we use the MySQL 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;

We can verify the change by adding some records 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 query data to see if the value of the task_id column is  increased by one each time you insert a new record:

1
2
SELECT task_id, description
FROM tasks

mysql-alter-table-autoincrement

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

Because of the new business requirement, we need to add a new column called complete to store percentage of completion for each task in the tasks table. In this case, we can use MySQL ALTER TABLE to add a new column 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 we don’t want to store description of tasks in the tasks table anymore so we have to remove it. The following command allows you to remove description column of the tasks table:

1
2
ALTER TABLE tasks
DROP COLUMN description;

Renaming table using MySQL ALTER TABLE statement

We can use MySQL 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.

We can rename our tasks table to work_items table as follows:

1
2
ALTER TABLE tasks
RENAME TO work_items;

In this tutorial, you’ve learned how to use MySQL ALTER TABLE statement to change existing table structure as well as to rename table.

 

From http://www.mysqltutorial.org/