How to add a column with a default value to an existing table
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_nameADD column_name data_type DEFAULT default_value;
Add a column with default value to table in My SQL
In SQL Server
ALTER TABLE table_nameADD column_name data_typeCONSTRAINT constraint_nameDEFAULT (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 StudentADD COUNTRY VARCHAR (20) DEFAULT 'India';/* Display table data */SELECT * FROM Student;
Explanation
- Line 2-7: We create a table,
Student. - Line 10-14: We insert a few values in the
Studenttable. - Line 17-18: We add a new column
COUNTRYto theScoretable with a default value,India. - Line 21: We display the data present in the
Scoretable.
Output
In the output, we can see the COUNTRY column with the default value, India, in the Student table.
TRENDING TOPICS
