Changing Table Structure Using MySQL ALTER TABLE

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

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:

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:

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:

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:

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:

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

Posts Related to Changing Table Structure Using MySQL ALTER TABLE

• Working with Tables – Part II

  Summary: following with the previous tutorial on how to create database tables in MySQL, in this tutorial, you will learn how to modify and remove ...

• Managing Database Index in MySQL

  Summary: In this tutorial, you will learn how to work with database index in MySQL. You will learn how to use database index properly to speed up ...

• Understanding MySQL TIMESTAMP

  Summary: The MySQL TIMESTAMP is a temporal data type that store combination of date and time values. In this tutorial you will learn how MySQL ...

• Updating Data in Database Tables

  Summary: Updating existing data is one of the most important task when you work with database. In this tutorial, you will learn how to use ...

• Working with Database Table – Part I

  Summary: In this tutorial you will learn how to create, show and describe tables in MySQL. Database table or table in short is one of  ...