In a relational database, when working with database tables, you might come across an instance where you need to add a new column to an already existing table. This can be an introduction of a new attribute to the table or just modifying the table schema.

In this guide, we will learn how to add a new column to an existing table starting with its basics, syntax, and practical demonstration.

Column Definition

Before adding a column to a table, we need to define the properties of the new column including its name, data type, and any constraints (such as NOT NULL, DEFAULT values, or foreign keys).

This is known as column definition where you provide everything that the database needs to know about the column and the data that should be stored in it.

Column definition is comprised of three main parts. These include:

• • Column Name – It specifies the name that you wish to assign the column.
  • Data type – It specifies the type of data that the column will store such as integer, text, etc.
  • Constraints – This specifies the optional constraints to be applied to the column. Examples include NOT NULL which tells the database engine that the column cannot store the null values, DEFAULT, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and more.

Add a Column to the Table in SQL

The following shows the basic syntax for adding a new column to an existing table. Keep in mind that there may be slight differences in the syntax depending on the database system:

 
We start with the ALTER TABLE clause which tells the database engine that we wish to modify the structure of an existing table.

We then specify the name of the table that we wish to modify.

Next, we use the ADD COLUMN clause which allows us to add a new column with the specified name, data type, and constraints.

Examples:

Let us look at the various examples on how we can add a new column to an existing table.

Example 1: Basic Usage

Suppose we have a basic table with four columns as shown in the following example query:

 
Next, let us assume that we wish to add a new column called “email” of type VARCHAR (100) to store the employee email addresses.

We can use the ALTER TABLE clause with ADD COLUMN as shown in the following example:

 
Example 2: Adding a Column with Constraints

We can also add a new column with various constraints as shown in the following example query:

 
In this example, we add a new column called “salary” with a NOT NULL constraint to ensure that all employees have a salary value.

Example 3: Adding Multiple Columns

The ALTER TABLE clause allows us to add multiple columns in a single statement as shown in the following example:

 
Here, we also add the “address” and “phone_number” columns to the “employees” table.

Conclusion

In this post, we explored one of the most common tasks in relational databases by learning how to add a new column to an existing table. We started with the basics, learned how to add a column with constraints, and more.