SQL Standard

Add a Column to the Table in SQL

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:

ALTER TABLE table_name
ADD COLUMN column_name data_type [column_constraints];

 
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:

    employee_id serial PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);

 
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:

ALTER TABLE employees
ADD COLUMN email VARCHAR(100);

 
Example 2: Adding a Column with Constraints

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

ALTER TABLE employees
ADD COLUMN salary DECIMAL(10, 2) NOT NULL;

 
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:

ALTER TABLE employees
ADD COLUMN address VARCHAR(100),
ADD COLUMN phone_number VARCHAR(15);

 
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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list