MySQL ALTER TABLE
MySQL ALTER TABLE Statement
An existing table’s columns can be added, removed, or changed using the ALTER TABLE statement.
It is also possible to add and remove different constraints from an existing table using the ALTER TABLE statement.
ALTER TABLE - ADD Column
The following syntax can be used to add a column to a table:
ALTER TABLE table_name
ADD column_name datatype;
The “Email” field is added to the “Customers” table using the SQL below:
Example
ALTER TABLE Customers
ADD Email varchar(255);
ALTER TABLE - DROP COLUMN
The following syntax can be used to remove a column from a table (keep in mind that some database systems prevent column deletions):
ALTER TABLE table_name
DROP COLUMN column_name;
The following SQL deletes the “Email” column from the “Customers” table:
Example
ALTER TABLE Customers
DROP COLUMN Email;
ALTER TABLE - MODIFY COLUMN
Use the following syntax to modify a column’s data type in a table:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
MySQL ALTER TABLE Example
Look at the “Persons” table:
| ID | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
The “Persons” table now needs a column called “DateOfBirth” added to it.
We make use of the SQL query that follows:
Example
ALTER TABLE Persons
ADD DateOfBirth date;
The new column “DateOfBirth” has a date type and will store a date, as you can see. What kind of data a column can include is determined by its data type. Visit our comprehensive Data Types guide for a comprehensive list of all the data types that MySQL supports.
This is how the “Persons” table will appear now:
| ID | LastName | FirstName | Address | City | DateOfBirth |
|---|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes | |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes | |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
Change Data Type Example
We now wish to modify the data type of the “DateOfBirth” column in the “Persons” table.
We make use of the SQL query that follows:
Example
ALTER TABLE Persons
MODIFY COLUMN DateOfBirth year;
It is evident that the “DateOfBirth” column has changed to a year type, displaying a year in either a two- or four-digit format.
DROP COLUMN Example
The “DateOfBirth” column in the “Persons” database should then be deleted.
We make use of the SQL query that follows:
Example
ALTER TABLE Persons
DROP COLUMN DateOfBirth;
The “Persons” table will now look like this:
| ID | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
