How to Remove Default Value from Column in MySQL

In this tutorial, we are going to see how to remove default value from a column in MySQL. To remove a default value from a column in MySQL, use ALTER TABLE … ALTER … DROP DEFAULT statement.
 

Syntax:

ALTER TABLE tableName ALTER COLUMN columnName DROP DEFAULT;

 

 

Example : How to Remove Default Value from a Column in MySQL

In “Employee” table, the column “age” has a default value of 0. 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  |      | NULL     |                |
+----------+-----------------+--------+------+----------+----------------+

To remove the default value ‘0’ in the “age” column, we run the following query:

ALTER TABLE Employee ALTER COLUMN age DROP DEFAULT;

Now we check if the default value of the column “age” has been deleted.

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  |      | NULL     |                |
+----------+-----------------+--------+------+----------+----------------+

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