After you have created a table in
SQL, you may realize that you forgot to add in a specific column that you need. For example, you may have created a database to store a list of suppliers, and forgot to include a field to store the category of goods the supplier offers.
In these situations, you can use the
SQL ALTER TABLE statement to amend an existing table. This statement allows you to add a column, change a column, or delete a column.
In this tutorial, we will break down the basics of the
SQL ALTER TABLE statement, and discuss how you can use it to manipulate a database.
Creating Tables Refresher
Databases are made up of a series of tables, each of which stores values in the form of records. If we want to store a list of employees, for example, we would need to create a table. Each individual employee would be added as a record in the table.
In order to create a table, we use the
SQL CREATE TABLE statement. The basic syntax for this statement is as follows:
CREATE TABLE table_name ( column_name data_type constraints; column_name data_type constraints; );
Let’s use an example to illustrate this statement in action. Below is an
SQL command that will create an employee database that stores three columns: the name of an employee, their title, and the branch for which they work.
CREATE TABLE employees ( name VARCHAR(50) NOT NULL, title VARCHAR(75) NOT NULL, branch VARCHAR(25) NOT NULL );
This statement creates a table with three columns, and each column is defined as a string. In addition, we have used the
NOT NULL statement to specify that each field should be filled out when we create a new record. If we wanted to, we could add in columns that store numbers, dates, or any other type of data.
But what if we’ve forgotten to add a column? Say we need to add a column that states whether an employee is on probation. That’s where the
ALTER TABLE statement comes in.
SQL Add Column
As we discussed above, we forgot to include a column that stores whether or not an employee is on probation. We can use the
ALTER TABLE statement to alter our existing table and add in this new column.
The basic syntax for adding a new column is as follows:
ALTER TABLE table_name ADD column_name data_type constraints;
SQL ALTER TABLE
add column statement we have written above takes four arguments: the name of our table, the name of the column we want to create, the data type we want that column to have, and any constraints we want our new column to have.
Here is an example of an
SQL add column statement in action:
ALTER TABLE employees ADD probation BOOLEAN;
Now our table appears as follows:
|Column Name||Data Type||Constraints|
The fourth column—probation—has been added to our table with the boolean data type.
It’s worth noting that if you want to add a
NOT NULL column to an existing table, you must specify a default value. You can do this using the
DEFAULT constraint. This is because
NOT NULL protects a database against null values, and that policy applies to existing records.
Here’s an example of this in action:
ALTER TABLE employees ADD probation BOOLEAN NOT NULL DEFAULT false;
Now our probation column has a
NOT NULL value, and every employee’s probation status has been set to
false in the database. Here’s a query that we can use to find employee names and their probation statuses:
SELECT name, probation FROM employees;
Our query returns the following:
SQL ALTER TABLE has a wide range of other functions. You can use the statement to change the position of a column, modify columns, or rename a column. These functions are outside of the scope of this article, but if you are interested in exploring more advanced
SQL, remember that you can do more with the
ALTER TABLE statement.
ALTER TABLE statement can be used to perform an
SQL add column function. If you need to add a new column to a table, you can use the
ALTER TABLE statement to add the column with any constraints you want to include.
Now you’re ready to start adding columns to your database tables like an expert!