Once you have created a table in SQL, you may realize that you have forgotten to add in a column or a constraint. Or maybe you have assigned the wrong name to a column and want to delete it from your table.
That’s where the SQL ALTER TABLE command comes in. The ALTER TABLE command allows you to change an existing table by adding, changing, or deleting a column in the table.
This tutorial will discuss, with reference to examples, how to alter a table using the SQL ALTER TABLE command.
In a database, data is stored in tables. Tables define the structure for how data should be stored, and contain records which are individual entries in a table.
For instance, a table may store data on employees who work for a business, and each record would contain information on each individual employee.
For this tutorial, we are going to be modifying a database table for a business that stores information about each employee who works for the business.
Our table is called “employees”, and contains the following values:
Now, suppose, 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.
Add a Column to a Table
One of the most common uses of the ALTER TABLE command is to add a column into an existing table. 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”, 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 table. 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.
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);
Here is the command we would use to add a constraint to our “phone_number” column:
ALTER TABLE employees ADD UNIQUE (phone_number);
If we wanted to add another constraint to our database—for example, a primary key—we could do so by replacing “UNIQUE” with the name of the constraint we want to add.
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 because we intend on creating 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:
Change Data Type of a Column
In addition, 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 as follows:
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:
Rename a Table
The ALTER TABLE command also allows you to rename a table in SQL. The syntax for doing so is as follows:
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;
In MySQL, instead of using ALTER TABLE, you can use the RENAME table command. We would use the following command to rename our table in MySQL:
RENAME TABLE employees TO old_employees_2019;
The SQL ALTER TABLE command allows you to change the structure of a table in SQL.
This command allows you to add a new column to a table, add constraints to a column, remove a column from a table, change the data type of a column, and rename a table.
This tutorial discussed, with reference to examples, how to use the ALTER TABLE command in SQL. Now you’re ready to start changing the structure of SQL tables like a pro!