MySQL ALTER TABLE Tutorial and Examples

In this article, we will describe how to use the ALTER TABLE statement.

In the process of using the table, if you need to modify the table, you can use the ALTER TABLE statement. With the ALTER TABLE statement, you can rename tables, rename columns, add columns, drop columns, modify column properties, etc.

ALTER TABLE syntax

ALTER TABLE table_name
  [alter_action options], ...

The alter_action is a modification action, and it may be:

  • The ADD keyword can be used to add columns, indexes, constraints, etc., including:

    • ADD [COLUMN]: add a column
    • ADD INDEX: add a index
    • ADD PRIMARY KEY: add a primary key constrain
    • ADD FOREIGN KEY: add a foreign key constrain
    • ADD UNIQUE INDEX: add a unique index
    • ADD CHECK: add a check constraint

  • The DROP keyword can be used to drop columns, indexes, constraints, etc., including:

    • DROP [COLUMN] col_name: delete a column
    • ADD INDEX index_name: drop a index
    • DROP PRIMARY KEY: delete the primary key constrain
    • DROP FOREIGN KEY fk_symbol: delete a foreign key constrain
    • DROP CHECK symbol: remove check constraint

  • The MODIFY keyword is used to modify the definition of a column. Unlike the CHANGE keyword, it cannot rename columns. For example: MODIFY [COLUMN] col_name column_definition.

  • The CHANGE keyword is used to modify the definition of a column. Unlike the MODIFY keyword, it can rename columns. For example: CHANGE [COLUMN] old_col_name new_col_name column_definition.

  • The RENAME keyword is used to rename columns, indexes, and tables. include:

    • RENAME COLUMN old_col_name TO new_col_name: Rename a column.
    • RENAME INDEX old_index_name TO new_index_name: Rename a index.
    • RENAME new_tbl_name: Rename a table.

ALTER TABLE Examples

From the ALTER TABLE syntax, you got that there are many usages for ALTER TABLE. To demonstrate the usage of ALTER TABLE, we create a table named user in the testdb database .

Please execute the following statement:

CREATE TABLE user (id INT);

Use the following statement to view the definition of the user table.

DESC user;

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

Let’s look at some practical examples below.

Add a column using ALTER TABLE statement

The following statement adds a column named name in the user table using the ADD keyword.

ALTER TABLE user
ADD name VARCHAR(20);

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Add multiple columns using ALTER TABLE statement

The following statement adds two columns age and email in the user table using the ADD keyword.

ALTER TABLE user
ADD age INT,
ADD email VARCHAR(50);

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Rename column using ALTER TABLE statement

The following statement uses the RENAME COLUMN keyword to rename the name column to username.

ALTER TABLE user
RENAME COLUMN name TO username;

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Here is the modified table definition:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Modify column definitions using ALTER TABLE statement

The following statement uses the MODIFY keyword to modify the username column from varchar(20) to VARCHAR(45).

ALTER TABLE user
MODIFY username VARCHAR(45);

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Here is the modified table definition:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(45) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Modify column names and definitions using ALTER TABLE statement

The following statement uses the CHANGE keyword to modify the username column to name VARCHAR(30).

ALTER TABLE user
CHANGE username name VARCHAR(30);

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Here is the modified table definition:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| email | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Add the primary key using ALTER TABLE statement

The following statement uses the ADD keyword to set the user table’s id column as the primary key.

ALTER TABLE user
ADD PRIMARY KEY(id);

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Note that since the primary key requires the value of the column to be unique and cannot be NULL, an error will be returned if there are duplicate values ​​or NULL values. Likewise, if you add a unique index, the same error may occur.

Here is the modified table definition:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| email | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Drop a column using ALTER TABLE statement

The following statement deletes the email column using the DROP keyword.

ALTER TABLE user
DROP COLUMN email;

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Note: Dropping a column is a dangerous operation because it permanently deletes the data in the column. This is an irreversible action, please be careful.

Here is the table definition after deletion:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Rename the table using ALTER TABLE statement

The following statement uses the RENAME keyword to rename the user table to users.

ALTER TABLE user
RENAME users;

We can also rename the table with the RENAME TABLE statement, which is used as follows:

RENAME TABLE table_name TO new_table_name;

The following statement also renames the user table to users:

RENAME TABLE user TO users;

Conclusion

In this article, we described how to use the ALTER TABLE statement modify a table, including: adding columns, deleting columns, modifying columns, renaming columns, renaming tables, setting primary keys, etc. You can use the following keywords in the ALTER TABLE statement:

  • The ALTER TABLE keyword is followed by the name of the table to modify.
  • The ADD keyword is used to add columns, indexes, constraints, etc.
  • The DROP keyword is used to drop columns, indexes, constraints, etc.
  • The RENAME keyword is used to rename columns, indexes, and tables.
  • The MODIFY keyword is used to modify the definition of a column.
  • The CHANGE keyword is used to modify column definitions and column names.
  • The RENAME TABLE ... TO ... is used to rename the table.