How to alter table and add column SQL [Practical Examples]

How to add column to table in SQL

In SQL, to add column(s) to the existing table SQL ALTER TABLE ADD statement is used, there is no statement like SQL ADD COLUMN to add the column or to modify the definition of SQL existing table, SQL ALTER TABLE statement is also used to delete, or modify columns in a table

We 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

 

Key points of SQL alter table add column statement

• Using the ALTER TABLE statement to add columns to a table automatically adds those columns to the end of the table
• When you add columns, you must specify the column name and data type. You can also specify optional properties.
• You cannot add primary keys to an existing table

 

Syntax of ALTER TABLE ADD COLUMN for SQL Server

ALTER TABLE table_name
ADD new_column_name data_type [constraint];

Here,

• ALTER TABLE: ALTER TABLE is a keyword used to change the definition of an existing table, written before the table name
• table_name: The name of the table is to be altered. If the table isn't in the current database or contained by the schema owned by the current user, you must explicitly specify the database and schema.
• ADD: ADD a keyword specifies before the named column is to be added or altered
• new_column_name: it is the name of the new column to be added in the table specified in the table_name, column name can be any valid string that does not contain space
• data_type: It specifies any valid data type supported by SQL

 

Syntax of ALTER TABLE ADD COLUMN for MySQL

ALTER TABLE table
ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];

Here,

• ALTER TABLE: ALTER TABLE is a keyword used to change the definition of an existing table, written before the table name
• table_name: The name of the table is to be altered. If the table isn't in the current database or contained by the schema owned by the current user, you must explicitly specify the database and schema
• ADD COLUMN: ADD COLUMN a keyword specifies before the named column is to be added or altered; COLUMN keyword is optional so you can omit it
• [FIRST|AFTER existing_column]: MySQL allows you to add the new column as the first column of the table by specifying the FIRST keyword. It also allows you to add the new column after an existing column using the AFTER existing_column clause. If you don’t explicitly specify the position of the new column, MySQL will add it as the last column

 

Examples of ALTER TABLE ADD COLUMN

Consider school student result management database with 3 tables student, result and subject to performing practical examples of Alter table add column statement
Student Table

Result Table

Subject Table

 

Add a column of integer type with no constrain

Example 1: Write SQL Query to add new column contact no in the student table

alter table tblstudent add contactno integer ;

In the above query, SQL alter table add column statement is used to modify the definition of student table by adding new column contactno which has data type as integer and with no constraint

OUTPUT:

To see the output of the above query, we need to view the student table definition

SQL Server : sp_columns tblstudent
Oracle: DESC tblstudent

 

Add a column of varchar type with size value and no constraint

Example 2 : Write SQL Query to add gender column with varchar datatype length of 7 characters in the student table

alter table tblstudent add gender varchar(7);

In the above query, SQL alter table add column statement is applied with table tblstudent to add a new column named gender having varchar datatype with a character length of 7

OUTPUT:

To see the output of the above query, we need to view the student table definition

SQL Server : sp_columns tblstudent
Oracle : DESC tblstudent

 

Add multiple columns using a single ALTER TABLE statement

We can add multiple columns to a table using the single SQL Server ALTER TABLE statement as below

ALTER TABLE table_name
ADD  new_column_name1  data_type [constraint],
new_column_name2  data_type [constraint],
new_column_name3  data_type [constraint];

Example 3 : Write SQL Query to add two columns  semester  as integer and examtype varchar type to the result table

alter table tblresult add semester integer , examtype varchar(15); 

• In the above query, SQL alter table add column statement is used to add multiple columns in the result table
• the first column named semester with the integer data type and the second column named examtype as the varchar datatype to be added using alter table add column statement

OUTPUT:

To see the output of the above query, we need to view the result table definition

SQL Server : sp_columns tblresult
Oracle : DESC tblresult

 

Adding a Column with a Default Constraints

The SQL Default constraint is used to specify the default value to be added when the new row is inserted into the table, we can specify the default constraint with the ALTER TABLE ADD COLUMN statement to add the column with its default value

Example 4 : Write SQL Query to add new column academic year in the result table with default value of 2019-2020

alter table tblresult add acadamic_year varchar(15) default '2019-2020'

In the above query, SQL ALTER TABLE ADD COLUMN statement is used to add column academic_year n the result table with default constraint having a default value of  ‘2019-2020’

OUTPUT:

To see the output of the above query, we need to view the result table definition

SQL Server : sp_columns tblresult
Oracle : DESC tblresult

the default value of 2019-2020 in the academic year column will automatically be added if we do not specify the value for an academic column with SQL insert statement

insert into tblresult(result_id,student_id,examname,examdate,subjectid,obtainmark,totalmarks,pecentage,grade,status,semester,examtype) values(10001,107,'sem5','02-02-2000',2,88,100,88,'A','pass',5,'regular')

OUTPUT:

SELECT result_id, student_id, examname, examdate, subjectid, obtainmark, totalmarks, pecentage, grade, status, semester, examtype, acadamic_year
FROM dbo.tblresult

 

Add a column with the NOT NULL constraint

We can also add a new column with NOT NULL constraint to restraint user to must insert data to the newly added column, if we add a new column by only specifying NOT NULL constraint, SQL Server will show error message as follow,

Example 5 : Write SQL Query to add emailed column in the student table with not null constraint

alter table tblsubject add facult_name varchar(20) not null default 'Reema madam'

• In the previous query, SQL ALTER TABLE ADD COLUMN statement is used to to add a new column named faculty name with the not-null constraint
• in this query, SQL default constraint is also specified after not null constraint because we cannot add null value to newly added column and by specifying default constraint we are given the default value for a new column

OUTPUT:

To see the output of the above query, we need to view the subject table definition

SQL Server : sp_columns tblsubject
Oracle: DESC tblsubject

 

Summary

In this article of add column to table sql/ sql alter table add column, we have covered an overview of SQL alter table add a column, key points to remember for SQL alter table add a column, the syntax of SQL alter table ass column for SQL Server and MySQL has been explained with syntax argument,  practical example of SQL alter table add a column without constraint and with NOT  NULL and DEFAULT constraint also be covered, SQL alter table add multiple columns with practical example is also been explained

 

References

Getting started with SQL - Detailed Explanation

 

Further Reading

Add Columns to a Table (Database Engine)