Changing Table Structure Using MySQL ALTER TABLE

Earn extra cash at home! Thousands of MySQL related projects are waiting for you to finish.

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 column data type, add primary key, rename table and a lot more. The following illustrates the MySQL ALTER TABLE syntax:

ALTER TABLE table_name action1[,action2,…]

Followed by the keyword ALTER TABLE is name of table that you want to make the changes. After the table name is the action you want apply to the table. An action can be anything from add a new column, add primary key…. to rename table. MySQL allows you to do multiple actions at a time, separated by a comma.

Let’s create a new table for practicing MySQL ALTER TABLE statement. We’re going to create a new table called tasks in our sample database classicmodels as follows:

CREATE  TABLE 'tasks' (
  'task_id' INT NOT NULL ,
  'subject' VARCHAR(45) NULL ,
  'start_date' DATETIME NULL ,
  'end_date' DATETIME 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 to add auto-increment for a column

Suppose we want the task id is increased by one automatically whenever we insert a new task. In order to accomplish this, we need to use the MySQL ALTER TABLE statement to change the column task id to make it auto increment as follows:

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

Using MySQL ALTER TABLE 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 completion percentage for each task in the tasks table. In this case, we can use MySQL ALTER TABLE to add a new column 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

Let’s say we don’t want to store task description in the task table anymore so we have to remove that column. Here is the SQL command to drop a column from a table:

ALTER TABLE tasks
DROP COLUMN description ;

Renaming table using MySQL ALTER TABLE statement

We can use MySQL ALTER table statement to rename a table. Note that before renaming a table you should take a serious consideration to see its dependencies from database to application level. We can rename our tasks table to work_items as follows:

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 and rename table.

Search