Summary: in this chapter, Explains how to use the MySQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table with syntax and examples.
Using MySQL ALTER TABLE To Change Table Structure
The MySQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The MySQL ALTER TABLE statement is also used to rename a table.
The following illustrates the ALTER TABLE statement syntax:
1
|
ALTER TABLE table_name table_action
|
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 practising the ALTER TABLE statement.
We’re going to create a new table named contacts in the
sample database. The following is the script for creating the contacts table.
1
2
3
4
5
6
7
|
CREATE TABLE contacts (
contact_id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);
|
Add single or multiple columns in table
Syntax: The syntax to add a columns in a table in MySQL is:
1
2
3
4
5
6
7
|
ALTER TABLE table_name
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
....
;
|
- table_name: The name of the table to modify.
- new_column_name: The name of the new column to add to the table.
- column_definition: The datatype and definition of the column (NULL or NOT NULL, etc).
- FIRST | AFTER column_name: It tells MySQL where in the table to create the column. If this parameter is not specified, the new column will be added to the end of the table.
Example of Add single column
Let’s look at an example that shows how to add a column in a MySQL table using the ALTER TABLE statement.
1
2
3
|
ALTER TABLE contacts
ADD address VARCHAR(100) NOT NULL
AFTER first_name;
|
This MySQL ALTER TABLE example will add a column called address to the contacts table. It will be created as a NOT NULL column and will appear after the first_name field in the table.
Example of Add multiple columns
Let’s look at an example that shows how to add multiple columns in a MySQL table using the ALTER TABLE statement.
1
2
3
4
5
|
ALTER TABLE contacts
ADD city VARCHAR(100) NOT NULL
AFTER address,
ADD country VARCHAR(100) NOT NULL
AFTER city;
|
Above ALTER TABLE example will add two columns to the contacts table – city and country.
Modify single or multiple columns in table
Syntax: The syntax to modify a column in a table in MySQL is:
1
2
3
4
5
6
|
ALTER TABLE table_name
MODIFY column_name column_definition
[ FIRST | AFTER column_name ],
MODIFY column_name column_definition
[ FIRST | AFTER column_name ],
...;
|
- table_name: The name of the table to modify.
- column_name: The name of the column to modify in the table.
- column_definition: The modified datatype and definition of the column (NULL or NOT NULL, etc).
- FIRST | AFTER column_name: It tells MySQL where in the table to position the column, if you wish to change its position.
Example of Modify single column
Let’s look at an example that shows how to modify a column in a MySQL table using the ALTER TABLE statement.
1
2
|
ALTER TABLE contacts
MODIFY last_name VARCHAR(50) NULL;
|
Above ALTER TABLE example will modify the column called last_name to be a data type of varchar(50) and force the column to allow NULL values.
Example of Modify multiple columns
Let’s look at an example that shows how to modify multiple columns in a MySQL table using the ALTER TABLE statement.
1
2
3
4
|
ALTER TABLE contacts
MODIFY last_name VARCHAR(55) NULL
AFTER contact_type,
MODIFY first_name VARCHAR(45) NOT NULL;
|
Above ALTER TABLE example will modify two columns to the contacts table – last_name and first_name.
The last_name field will be changed to a varchar(55) NULL column and will appear after the contact_type column in the table. The first_name column will be modified to a varchar(45) NOT NULL column (and will not change position in the contacts table definition, as there is no FIRST | AFTER specified).
Drop column in table
Syntax: The syntax to drop a column in a table in MySQL using the ALTER TABLE statement is:
1
2
|
ALTER TABLE table_name
DROP COLUMN column_name;
|
- table_name: The name of the table to modify.
- column_name: The name of the column to delete from the table.
Example
Let’s look at an example that shows how to drop a column in a MySQL table using the ALTER TABLE statement.
1
2
|
ALTER TABLE contacts
DROP COLUMN birthday;
|
This ALTER TABLE example will drop the column called birthday from the table called contacts.
Rename column in table
Syntax: The syntax to rename a column in a table in MySQL using the ALTER TABLE statement is:
1
2
3
4
|
ALTER TABLE table_name
CHANGE COLUMN old_name new_name
column_definition
[ FIRST | AFTER column_name ]
|
- table_name: The name of the table to modify.
- old_name: The column to rename.
- new_name: The new name for the column.
- column_definition: The datatype and definition of the column (NULL or NOT NULL, etc). You must specify the column definition when renaming the column, even if it does not change.
- FIRST | AFTER column_name: Optional. It tells MySQL where in the table to position the column, if you wish to change its position.
Example
Let’s look at an example that shows how to rename a column in a MySQL table using the ALTER TABLE statement.
1
2
3
|
ALTER TABLE contacts
CHANGE COLUMN last_name lastname
VARCHAR(45) NOT NULL;
|
This MySQL ALTER TABLE example will rename the column called last_name to lastname. The column will be defined as a varchar(45) NOT NULL column.
Rename table
Syntax: The syntax to rename a table in MySQL is:
1
2
|
ALTER TABLE table_name
RENAME TO new_table_name;
|
- table_name: The table to rename.
- new_table_name: The new table name to use.
Example
Let’s look at an example that shows how to rename a table in MySQL using the ALTER TABLE statement.
1
2
|
ALTER TABLE contacts
RENAME TO cont_pepople;
|
This ALTER TABLE example will rename the contacts table to cont_people.
In this chapter, We’ve learnt how to use the MySQL ALTER TABLE statement to change existing table structure and to rename the table.
MySQL Sequence
MySQL DROP TABLE
