-->

MySQL ALTER TABLE

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

at
Labels: