MySQL Add Column With Default Value

In this tutorial, we are going to see how to add a column with a default value and how to add a default value to a column that already exists in a MySQL table.
 

Add Column With Default Value

When adding a column to a table using ALTER, we can also specify a default value. In the example below, by using ALTER, the column “Address” is added with a default value “New York” to “Employee” table.
 

Alter table Employee ADD(Address Varchar(10) Default 'New York');

Now we can check the default value of the column ‘Address’, by running the following command.

DESCRIBE Employee;

Output:

+----------+-----------------+--------+------+----------+----------------+
|  Field   |       Type      |  Null  |  Key |  Default |     Extra      | 
+----------+-----------------+--------+------+----------+----------------+
|  EmpID   | int<11>         |   No   |  PRI | NULL     | auto_increment | 
|  Age     | int<10>         |   Yes  |      | NULL     |                |
|  Name    | varchar<20>     |   Yes  |      | NULL     |                |
|  Address | varchar<10>     |   Yes  |      | New York |                |
+----------+-----------------+--------+------+----------+----------------+

 

Add a default value to a column that already exists

To add a default value to a column in MySQL, use the ALTER TABLE … ALTER COLUMN … SET DEFAULT statement. Here is an example:

ALTER TABLE Employee ALTER COLUMN age SET DEFAULT 0;

Now we can check the default value of the ‘age’ column, by running the following command.

DESCRIBE Employee;

Output:

+----------+-----------------+--------+------+----------+----------------+
|  Field   |       Type      |  Null  |  Key |  Default |     Extra      | 
+----------+-----------------+--------+------+----------+----------------+
|  EmpID   | int<11>         |   No   |  PRI | NULL     | auto_increment | 
|  Age     | int<10>         |   Yes  |      | 0        |                |
|  Name    | varchar<20>     |   Yes  |      | NULL     |                |
|  Address | varchar<10>     |   Yes  |      | New York |                |
+----------+-----------------+--------+------+----------+----------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More