SQL ALTER TABLE: A Guide

The SQL ALTER TABLE statement adds, changes, or removes a column in a table. This statement lets you modify a table after it has been created, even if the table stores records.

You may decide that you need to make a change to an SQL table. For instance, you may want to rename a column or add a new column to the table. These changes are all possible with the SQL ALTER TABLE command.

SQL ALTER TABLE

The ALTER TABLE command allows you to change an existing table by:

• Adding a column.
• Adding a constraint.
• Removing a column.
• Changing a data type.
• Renaming a column.

This tutorial will discuss, with reference to examples, how to alter a table using the SQL ALTER TABLE command.

Setting Up a Table

For this tutorial, we are going to modify a database table for a business. This database stores information about each employee who works for the business. Our table is called “employees” and contains the following values:

After creating our table, we have realized that we want to alter its structure. We could do so by using the SQL ALTER TABLE command.

To learn how to create a table, check out our SQL CREATE TABLE guide.

SQL ALTER TABLE: Add a Column to a Table

You can add a column to an SQL table using the ALTER TABLE command. The syntax for adding a new column into an existing table is as follows:

ALTER TABLE table_name ADD column_name data_type constraints;

Suppose that we want to add a new column into our “employees” table which stores the phone number of every employee. This column should be called “phone_number”. The column should use the VARCHAR() data type—which allows us to store strings with a maximum length—and have no constraints.

We could create this column using the following statement:

ALTER TABLE employees ADD phone_number VARCHAR(60);

This command adds a new column to our a table in an “employees” database. Now, when we look at the structure of our database, we have one new column called “phone_number”:

If you’re interested in learning more about how to add a column to a database in SQL, check out our SQL add column guide.

SQL ALTER TABLE: Add a Constraint to a Table

In the last section, we added a new column to our table: phone_number. But, we forgot to add in any constraints to the table.

In this example, this means that our database could potentially store the same phone number for multiple employees, which is not practical.

To fix this, we could add a UNIQUE constraint to the “phone_number” column in our table. The syntax for adding a constraint to a column in SQL is as follows:

ALTER TABLE table_name ADD UNIQUE (column_name);

This command adds a constraint to our “phone_number” column:

ALTER TABLE employees ADD UNIQUE (phone_number);

If we wanted to add another constraint, we could do so by replacing “UNIQUE” with another SQL constraint.

SQL ALTER TABLE: Remove a Column from a Table

You can also use the ALTER TABLE command to remove a column from a table. The syntax for performing this operation is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

Suppose we want to remove the column “department” from our table. We intend to create a new column later on that links an employee’s department to another table. We can remove this column using the following command:

ALTER TABLE employees DROP COLUMN department;

Upon executing this command, the “department” column from our table is dropped. So, the structure for our table now appears as follows:

SQL ALTER TABLE: Change Data Type of a Column

You can use the ALTER TABLE command to change the data type of a column. The syntax for changing the data type of a column is:

ALTER TABLE table_name ALTER COLUMN column_name new_data_type;

This command has two alternate versions for MySQL and Oracle (pre-10G), and for Oracle 10G and later. These are:

ALTER TABLE table_name MODIFY COLUMN column_name new_data_type; (MySQL, Oracle pre-10G)
ALTER TABLE table_name MODIFY column_name new_data_type; (Oracle 10G+ and later)

When we created our initial “employees” table, we made the mistake of assigning our “employee_number” field to be a VARCHAR() instead of a number. To rectify this mistake, we could use the following command:

ALTER TABLE employees ALTER COLUMN employee_number INT;

This command changes the type of our “employee_number” column to INT. So, the new structure for our database is as follows:

SQL ALTER TABLE: Rename a Table

The ALTER TABLE command also allows you to rename an SQL table. The syntax for renaming a table is:

ALTER TABLE current_table RENAME new_table;

Suppose we want to rename our table “employees” to “old_employees_2019”. We could do so using this command:

ALTER TABLE employees RENAME old_employees_2019;

You cannot rename a table using the ALTER TABLE command in MySQL. In MySQL, instead of using ALTER TABLE, you can use the RENAME table command.

"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"

Venus, Software Engineer at Rockbot

Find Your Bootcamp Match

We would use the following command to rename our table in MySQL:

RENAME TABLE employees TO old_employees_2019;

Conclusion

The SQL ALTER TABLE command allows you to change the structure of a table in SQL. You can add a table to, remove a table from, or change a table in a database. Further, you can modify the contraints associated with a table, such as UNIQUE or NOT NULL.

Do you want to learn more about SQL? Read our How to Learn SQL guide. You’ll find top tips on how to learn SQL. You will also find a list of expert-curated learning resources to help you build your knowledge.