How To Add A Column With A Default Value To An Existing Table In SQL

How to add a column with a default value to an existing table in SQL

• How-Tos FAQs
• December 17, 2018

To SQL add a column with a default value is a simple operation in SQL.

Let us set up a ‘student’ table as below:

CREATE TABLE student (     student_id INT ,     student_name VARCHAR(50),     major VARCHAR(50),     batch INT ); INSERT INTO student(student_id, student_name, major, batch) VALUES (2, 'Dave', 'Medicine', 2017); INSERT INTO student(student_id, student_name, major, batch) VALUES (100, 'Jack', 'Arts', 2010); INSERT INTO student(student_id, student_name, major, batch) VALUES (12, 'Rose', 'Computer', 2012);

SQL SERVER: Now, if we need to add a column named ‘country’ with the default value ‘USA,’ we add it using the below query.

-- Altered 'student' table to add a new column 'country' with default value 'USA'. -- Running this query will add the column along with defaulting its value as 'USA' in all previously existing rows. -- For SQL SERVER ALTER TABLE student     ADD country VARCHAR(50) NOT NULL CONSTRAINT cons_student_country     DEFAULT ('USA'); SELECT * FROM student -- Output -- New column country is added and the value is defaulted to 'USA' student_id student_name   major     batch      country -------------------------------------------------------------- 2           Dave           Medicine  2017        USA 100         Jack           Arts       2010        USA 12          Rose           Computer   2012        USA -- If we don't give any value in 'country' column in any newly add row 'USA' will be taken as its default value INSERT INTO student(student_id, student_name, major, batch) VALUES (55, 'Joe', 'History', 2016); -- If we give a value in 'country' column in newly add row, the given data will be add instead of default value INSERT INTO student(student_id, student_name, major, batch, country) VALUES (55, 'Logan', 'History', 2016, 'Canada'); SELECT * FROM student -- Output student_id student_name    major     batch      country -------------------------------------------------------------- 2          Dave            Medicine  2017        USA 100        Jack            Arts       2010        USA 12         Rose            Computer   2012        USA 55         Joe             History      2016        USA 56         Logan           History      2016        Canada

MySQL:

-- Altered 'student' table to add a new column 'country' with default value 'USA'. -- Running this query will add the column along with defaulting its value as 'USA' in all previously existing rows.   -- For MySQL ALTER TABLE student ADD (country VARCHAR(50) NOT NULL DEFAULT 'USA'); SELECT * FROM student -- Output -- New column country is added and the value has defaulted to 'USA' student_id student_name    major     batch      country -------------------------------------------------------------- 2            Dave           Medicine  2017        USA 100         Jack           Arts       2010        USA 12           Rose           Computer   2012        USA -- If we don't give any value in 'country' column in any newly add row 'USA' will be taken as its default value INSERT INTO student(student_id, student_name, major, batch) VALUES (55, 'Joe', 'History', 2016); -- If we give a value in 'country' column in newly add row, the given data will be add instead of default value INSERT INTO student(student_id, student_name, major, batch, country) VALUES (55, 'Logan', 'History', 2016, 'Canada'); SELECT * FROM student -- Output student_id student_name    major     batch      country -------------------------------------------------------------- 2           Dave            Medicine  2017        USA 100         Jack            Arts       2010        USA 12          Rose            Computer   2012        USA 55          Joe             History     2016        USA 56          Logan           History     2016        Canada

Up Next:

Read How to use the command line to import SQL files in MySQL?