Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

mysql
communitycreator
table
value

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

Shubham Singh Kshatriya

Overview

To add a new column to an existing table, we use the ALTER TABLE command to add a new column to an existing. To set a default value, we use the DEFAULT constraint.

Now, to add a column with a default value to an existing table, we can use them together as below.

In MySQL

ALTER TABLE table_name
ADD column_name data_type DEFAULT default_value;
Add a column with default value to table in My SQL

In SQL Server

ALTER TABLE table_name
ADD column_name data_type 
CONSTRAINT constraint_name
DEFAULT (default_value);
Add a column with default value to table in SQL Server

Example

Let's see an example of adding a column with a default value to an existing table in MySQL.

/* Create table Student */
CREATE TABLE Student(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,      
   PRIMARY KEY (ID)
);

/* Insert values in table Student */
INSERT INTO Student (ID, NAME, AGE)
VALUES (1, 'Shubh', 22);

INSERT INTO Student (ID, NAME, AGE)
VALUES (2, 'Kush', 21);

/* Add a new colum with default value */
ALTER TABLE Student
ADD COUNTRY VARCHAR (20) DEFAULT 'India';

/* Display table data */
SELECT * FROM Student;
Example to add a column with default value to table in My SQL

Explanation

  • Line 2-7: We create a table, Student.
  • Line 10-14: We insert a few values in the Student table.
  • Line 17-18: We add a new column COUNTRY to the Score table with a default value, India.
  • Line 21: We display the data present in the Score table.

Output

In the output, we can see the COUNTRY column with the default value, India, in the Student table.

RELATED TAGS

mysql
communitycreator
table
value

CONTRIBUTOR

Shubham Singh Kshatriya
RELATED COURSES

View all Courses

Keep Exploring