SQL add column with default value to an existing table [SOLVED]

Overview of default value constraints in SQL

SQL default constraint is specified at the time of table creation or can be added with SQL ALTER table. SQL default constraint is used to specify a default value for a column; the value will be inserted into all new records if a value for that particular column is not set.

SQL ALTER TABLE statement is used to make changes in the existing SQL table. It is used to add, remove or modify columns and add or remove constraints from an existing table. SQL ALTER TABLE ADD COLUMN statement is used to add a new column in the existing SQL table.

 

Syntax of SQL ALTER TABLE statement

ALTER TABLE table_name
ADD  column_name  datatype  column_constraint;

Here,

• table_name: Specify table name in which column will be added
• column_name: Specify the name of a column to be added, with datatype
• column_constraint: Specify constraint name to be applied on a column

 

Syntax of SQL add column with default value

ALTER TABLE table_name
ADD  column_name  datatype  DEFAULT default_value;

Here,

• DEFAULT: Keyword used to specify default constraint
• default_value: Specify the default value for a column

 

Examples of SQL add column with default value

Consider student result management database with three tables student, result, and subject to perform SQL add  column with default value examples

Student table

 

Result table

 

Subject table

 

SQL ADD Single column of int datatype column with default value

Example 1: Write SQL query to add new column semester in existing result table

alter table tblresult add semester int default 3

• In the above query, SQL alter table statement is applied to add a new column semester of int type in result table with default constraint
• Default value 3 has been specified with default constraint which will automatically add when a new record is inserted.

OUTPUT:

INSERT INTO tblresult(result_id, student_id, examname, examdate, subjectid, obtainmark, totalmarks, pecentage, grade, status)
VALUES (1010, 108, 'sem3', '02-02-2000', 1, 76, 100, 76, 'A', 'pass')

In the above SQL insert query, the value for the newly added column semester does not specify. When we execute the above query new record will be inserted and a default value for the semester column will be added automatically.

SELECT  result_id, student_id, examname, examdate, subjectid, obtainmark, totalmarks, pecentage, grade, status, semester
FROM  tblresult

 

SQL ADD Single Varchar datatype column with default value

In SQL to add a new column of varchar datatype with a default value, we need to specify character length with Varchar type and also need to define a default value in the single quotation.

Example 2: Write SQL query to add new column address of varchar type with character length 30 and default value as ‘Althan, Surat’ in the student table

alter table tblstudent add address varchar(30) default 'althan, surat'

• In the above query, SQL alter table add column is used to add a new column address in the student table
• The default constraint is also specified with new column address with default value ‘Althan,Surat'

OUTPUT:

INSERT INTO tblstudent(student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id)
VALUES(109, 'kiya', 30005, '09-09-2001', 'e163030303', '01-01-1992', 'kiya@gmail.com', 'Surat', 3);

In the above SQL insert query, the value for the newly added column address does not specify. When we execute the above query new record will be inserted and a default value for the address column as ‘althan, Surat’ will be added automatically.

SELECT  student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id, address, age, phone
FROM  tblstudent

 

SQL Add multiple columns of int type with default value

We can add multiple columns in a table using a single SQL alter table statement separated by comma(,).  We need to specify each new column name with datatype and default value.

Example 3: Write SQL query to add two new columns age and phone of int datatype in student table

alter table tblstudent add age int default 17, phone int default 98989

• In the above query, SQL alter table statement is used to alter existing table student by adding two new columns age and phone of int data type
• the default constraint is also applied on both columns age with a default value 17 and phone with a default value as 98989

OUTPUT:

INSERT INTO tblstudent(student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id)
VALUES (111, 'riya', 30005, '09-09-2002', 'e163030313', '01-01-1992', 'riya@gmail.com', 'Surat', 3)

In the above SQL insert query, values for recently added columns age and phone do not specify. When we execute the above query new record will be inserted and a default value for the age column as 17 and the phone column as ‘98989’ will be added automatically.

SELECT  student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id, address, age, phone
FROM   tblstudent

 

SQL Add multiple columns of varchar type with default value

Example 4: Write SQL query to add two columns exam_type and remark in result table with default value as ‘external’ and ‘present’

alter table tblresult add exam_type varchar(20) default 'external', remark varchar(10) default 'present'

• In the above query, SQL alter table statement is used to alter existing table student by adding two new columns exam_type and remark of varchar datatype
• The default constraint is applied on both the columns exam_type and remark

OUTPUT:

INSERT INTO tblresult (result_id, student_id, examname, examdate, subjectid, obtainmark, totalmarks, pecentage, grade, status, semester)
VALUES (1021, 110, 'sem2', '02-02-2002', 2, 94, 100, 76, 'A', 'pass', 2)

In the above SQL insert query, values for recently added columns exam_type and remark do not specify. When we execute the above query new record will be inserted and a default value for the exam_type column as external and remark column as ‘present’ will be added automatically.

SELECT  result_id, student_id, examname, examdate, subjectid, obtainmark, totalmarks, pecentage, grade, status, semester, exam_type, remark
FROM  tblresult

 

SQL Add multiple columns with default and NOT NULL constraint

In SQL we can apply more than one constraint to a single column. We can apply default and NOT NULL constraints on a column separated by space. The NOT NULL constraint is used to enforce the user to enter a value in the specified column.

Example 5: Write SQL query to add new column faculty_contact of varchar type with default value as ‘90909090’ and NOT NULL constraint in the subject table

alter table tblsubject add faculty_number varchar(10) default '90909090' NOT NULL;

• In the above query, SQL alter table statement is used to alter subject table by adding new column faculty_number of varchar type.
• In the above query two constraints have been applied to faculty_number column. SQL default constraint to assign a default value as ‘90909090’ and NOT NULL constraint.

OUTPUT:

INSERT INTO tblsubject (subjectid, facultname, subjectname, subjectcode)
VALUES (4, 'ram', 'java', 1005)

In the above SQL insert query, the value for the recently added column faculty_number does not specify. When we execute the above query new record will be inserted and a default value for faculty_number column as ‘90909090’ will be added automatically.

SELECT   subjectid, facultname, subjectname, subjectcode, faculty_number
FROM  tblsubject

 

Summary

In this article on SQL add column with default value, We have covered an overview of how to add a new column in an existing table, what is default constraint, the Syntax of SQL alter table add column, the syntax of SQL alter table add column with default constraint, also explain SQL add a single column with default constraint, SQL add multiple columns with default constraint with practical examples.

 

References

SQL alter table
SQL constraints

 

Read More

SQL specify default values for columns