Description

The ALTER TABLE statement can be used to alter or modify an existing table.

  • It can be used to add, modify, or delete table columns.
  • It can be used to add and drop table constraints.
  • It can be used to rename a table.

After creating a table, we may get into a situation where we need to modify the table definition of a table that is in use. In such situations, the ALTER TABLE statement comes handy to do such changes on an existing table without corrupting the table data.

Let's look at some common use cases of the ALTER TABLE statement.

Syntax

Here is the basic syntax for each statement.

  • The table_name and new_table_name represents the current and new table names.
  • The column_name represents the column name to be altered.
  • The existing_column represents any existing column name on the table.
  • The column_definition represents the column definition, which can include its data type and constraints in case of any.
Purpose ALTER TABLE Statement Syntax
Add Column ALTER TABLE table_name
ADD [COLUMN] column_name column_definition
[FIRST|AFTER existing_column];
Modify Column ALTER TABLE table_name
MODIFY column_name column_definition
[FIRST|AFTER existing_column];
Drop Column
ALTER TABLE table_name 
DROP COLUMN column_name;
Rename Table ALTER TABLE table_name
RENAME new_table_name;
Add Constraint ALTER TABLE table_name 
ADD UNIQUE (column_name,...);

ALTER TABLE table_name 
ADD PRIMARY KEY (column_name,...);

Table Data

Before executing any of the queries, consider the below table data.

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo Japan 100
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100
5 Thomas Hardy 89 Chiaroscuro Rd. Portland USA 97219

Add Column

The below SQL ALTER TABLE statement adds a new column to the table.

Run this on IDE

ALTER TABLE customers
ADD COLUMN email CHAR(50);

After successful execution, the table data looks as below.

customer_id customer_name address city country postal_code email
1 Maria Anders Obere Str. 57 Berlin Germany 12209 NULL
2 Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo Japan 100 NULL
3 Dominique Perrier 25, rue Lauriston Paris France 75016 NULL
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100 NULL
5 Thomas Hardy 89 Chiaroscuro Rd. Portland USA 97219 NULL

Modify Column Data Type

The below SQL ALTER TABLE statement modifies an existing column on the table, by changing its data type.

Run this on IDE

ALTER TABLE customers
MODIFY email VARCHAR(50);

After successful execution, the table data looks as below.

customer_id customer_name address city country postal_code email
1 Maria Anders Obere Str. 57 Berlin Germany 12209 NULL
2 Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo Japan 100 NULL
3 Dominique Perrier 25, rue Lauriston Paris France 75016 NULL
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100 NULL
5 Thomas Hardy 89 Chiaroscuro Rd. Portland USA 97219 NULL

Modify Column Position

The below SQL ALTER TABLE statement modifies an existing column on the table, by changing its position.

Run this on IDE

ALTER TABLE customers
MODIFY email VARCHAR(50)
AFTER customer_name;

After successful execution, the table data looks as below.

customer_id customer_name email address city country postal_code
1 Maria Anders NULL Obere Str. 57 Berlin Germany 12209
2 Yoshi Nagase NULL 9-8 Sekimai Musashino-shi Tokyo Japan 100
3 Dominique Perrier NULL 25, rue Lauriston Paris France 75016
4 Martin Blank NULL Via Monte Bianco 34 Turin Italy 10100
5 Thomas Hardy NULL 89 Chiaroscuro Rd. Portland USA 97219

Drop Column

The below SQL ALTER TABLE statement drops an existing column on the table.

Run this on IDE

ALTER TABLE customers
DROP COLUMN address;

After successful execution, the table data looks as below.

customer_id customer_name city country postal_code
1 Maria Anders Berlin Germany 12209
2 Yoshi Nagase Tokyo Japan 100
3 Dominique Perrier Paris France 75016
4 Martin Blank Turin Italy 10100
5 Thomas Hardy Portland USA 97219

Add Constraint

The below SQL ALTER TABLE statement adds a constraint to the table.

Run this on IDE

ALTER TABLE customers 
ADD UNIQUE (customer_name);

After successful execution, the table data looks as below.

  • From here on the table will not allow duplicate values in the customers_name column.
customer_id customer_name email address city country postal_code
1 Maria Anders NULL Obere Str. 57 Berlin Germany 12209
2 Yoshi Nagase NULL 9-8 Sekimai Musashino-shi Tokyo Japan 100
3 Dominique Perrier NULL 25, rue Lauriston Paris France 75016
4 Martin Blank NULL Via Monte Bianco 34 Turin Italy 10100
5 Thomas Hardy NULL 89 Chiaroscuro Rd. Portland USA 97219

Rename Table

The below SQL ALTER TABLE statement renames the table from "customers" to "customer.

Run this on IDE

ALTER TABLE customers
RENAME customer;

After successful execution, the table "customers" will no more exist, and the new table "customer" contains the table data.

Tips & Recommendations

Never try to alter a table unless it's absolutely needed.

Overall

We now know how to use ALTER TABLE statement to alter table definition.

Related Links