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


Written by - Falguni Thakker
Reviewed by - Deepak Prasad

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

student_id studentname admissionno admissiondate enrollmentno date_of_birth email city class_id
101 reema 10001 02-02-2000 e15200002 02-02-1990 reema@gmail.com surat 2
102 kriya 10002 04-05-2001 e16200003 04-08-1991 kriya@gmail.com surat 1
103 meena 10003 06-05-1999 e15200004 02-09-1989 meena@gmail.com vadodara 3
104 carlin 2001 04-01-1998 e14200001 04-04-1989 carli@gmail.com vapi 1
105 dhiren 2002 02-02-1997 e13400002 02-02-1987 dhiru@gmail.com vapi 2
106 hiren 2003 01-01-1997 e13400001 03-03-1887 hiren@gmail.com surat 2
107 mahir 10004 06-09-2000 e15200003 07-09-1990 mahi@gmail.com vapi 3
108 nishi 2004 02-04-2001 e16200001 03-02-1991 nishi@gmail.com vadodara 1

Result Table

result_id student_id examname examdate subject obtainmark totalmarks percentage grade status
3001 101 sem1 07-08-2001 1 80 100 80 A+ pass
3002 101 sem1 08-08-2001 2 76 100 76 A+ pass
3003 102 sem3 05-05-2000 3 67 100 67 A pass
3004 102 sem3 06-05-2000 4 89 100 89 A+ pass
3005 102 sem3 07-05-2000 5 90 100 90 A+ pass
3006 103 sem5 08-09-1998 6 55 100 55 B pass
3007 103 sem5 09-09-1998 7 30 100 30 D fail
3008 103 sem5 10-09-1998 8 34 100 34 D fail

Subject Table

subjectid facultyname subjectname subjectcode
1 krishna c 1003
2 rahul cpp 1004
3 radha asp 1005
4 meera sql 1006
5 yasoda cloud 1007
6 nadan cg 1008

 

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

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

 

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

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

 

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

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

 

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

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

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

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

 

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,

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

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

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

 

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)

 

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on LinkedIn.

Categories SQL

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment

X