Beginners Guide to SQL – SQL ALTER TABLE Statement

(SQL Tutorials for Citizen Data Scientist)


In this tutorial you will learn how to alter or modify an existing table using SQL.

Modifying Existing Tables

It is quite possible that after creating a table, as you start using it, you may discover you’ve forgot to mention any column or constraint or specified a wrong name for the column.

In such situation you can use the ALTER TABLE statement to alter or change an existing table by adding, changing, or deleting a column in the table.

Consider we’ve a shippers table in our database, whose structure is as follows:

| Field        | Type        | Null | Key | Default | Extra          |
| shipper_id   | int         | NO   | PRI | NULL    | auto_increment |
| shipper_name | varchar(60) | NO   |     | NULL    |                |
| phone        | varchar(60) | NO   |     | NULL    |                |

We’ll use this shippers table for all of our ALTER TABLE statements.

Now suppose that we want to expand the existing shippers table by adding one more column. But, the question is how we can do this using SQL commands? Well let’s find out.

Adding a New Column

The basic syntax for adding a new column to an existing table can be given with:

ALTER TABLE table_name ADD column_name data_type constraints;

The following statement adds a new column fax to the shippers table.


ALTER TABLE shippers ADD fax VARCHAR(20);

Now, after executing the above statement if you see the table structure using the command DESCRIBE shippers; on MySQL command-line, it looks as follow:

| Field        | Type        | Null | Key | Default | Extra          |
| shipper_id   | int         | NO   | PRI | NULL    | auto_increment |
| shipper_name | varchar(60) | NO   |     | NULL    |                |
| phone        | varchar(60) | NO   |     | NULL    |                |
| fax          | varchar(20) | YES  |     | NULL    |                |

Note: If you want to add a NOT NULL column to an existing table then you must specify an explicit default value. This default value is used to populate the new column for every row that already exists in your table.

Tip: When adding a new column to the table, if neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.

MySQL add new columns at the end by default. However, if you want to add a new column after a specific column you can use the AFTER clause, as follow:

mysql> ALTER TABLE shippers ADD fax VARCHAR(20AFTER shipper_name;

MySQL provide another clause FIRST that you can use to add a new column at first place within a table. Just replace the clause AFTER with FIRST in the previous example to add the column fax at the beginning of the shippers table.

Changing Column Position

In MySQL, if you’ve already created a table but unhappy with the existing column position within the table, you can change it any time using the following syntax:

ALTER TABLE table_name
MODIFY column_name column_definition AFTER column_name;

The following statement place the column fax after shipper_name column in shippers table.

mysql> ALTER TABLE shippers MODIFY fax VARCHAR(20AFTER shipper_name;

Adding Constraints

Our current shippers table has one major problem. If you insert records with duplicate phone numbers it wouldn’t stop you from doing that, which is not good, it should be unique.

You can fix this by adding a constraint UNIQUE to the phone column. The basic syntax for adding this constraint to existing table columns can be given with:

ALTER TABLE table_name ADD UNIQUE (column_name,…);

The following statement adds a constraint UNIQUE to the phone column.

mysql> ALTER TABLE shippers ADD UNIQUE (phone);

After executing this statement if you try to insert a duplicate phone number, you’ll get an error.

Similarly, if you’ve created a table without a PRIMARY KEY, you can add one with:

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

The following statement adds a constraint PRIMARY KEY to the shipper_id column, if not defined.

mysql> ALTER TABLE shippers ADD PRIMARY KEY (shipper_id);

Removing Columns

The basic syntax for removing a column from an existing table can be given with:

ALTER TABLE table_name DROP COLUMN column_name;

The following statement removes our newly added column fax from the shippers table.

mysql> ALTER TABLE shippers DROP COLUMN fax;

Now, after executing the above statement if you see the table structure, it looks as follow:

| Field        | Type        | Null | Key | Default | Extra          |
| shipper_id   | int         | NO   | PRI | NULL    | auto_increment |
| shipper_name | varchar(60) | NO   |     | NULL    |                |
| phone        | varchar(20) | NO   | UNI | NULL    |                |

Changing Data Type of a Column

You can modify the data type of a column in SQL Server by using the ALTER clause, as follow:

ALTER TABLE table_name ALTER COLUMN column_name new_data_type;

The MySQL database server however does not support the ALTER COLUMN syntax. It supports an alternate MODIFY clause that you can use to modify the column as follows:

ALTER TABLE table_name MODIFY column_name new_data_type;

The following statement changes the current data type of the phone column in our shippers table from VARCHAR to CHAR and length from 20 to 15.

mysql> ALTER TABLE shippers MODIFY phone CHAR(15);

Similarly, you can use the MODIFY clause to switch between whether a column in the MySQL table should allow null values or not by re-specifying the existing column definition and add the NULL or NOT NULL constraint at the end, like this:

mysql> ALTER TABLE shippers MODIFY shipper_name CHAR(15NOT NULL;

Renaming Tables

The basic syntax for renaming an existing table in MySQL can be given with:

ALTER TABLE current_table_name RENAME new_column_name;

The following statement renames our shippers table shipper.

mysql> ALTER TABLE shippers RENAME shipper;

You can also achieve the same thing in MySQL using the RENAME TABLE statement, as follow:

mysql> RENAME TABLE shippers TO shipper;

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!