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.
ALTER TABLE table_nameADD column_name data_type DEFAULT default_value;
ALTER TABLE table_nameADD column_name data_typeCONSTRAINT constraint_nameDEFAULT (default_value);
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;
Student.Student table.COUNTRY to the Score table with a default value, India.Score table.In the output, we can see the COUNTRY column with the default value, India, in the Student table.