How to Remove Default Value of Column in MySQL

Last updated on July 22nd, 2024 at 06:26 am

MySQL is a popular database used by many websites and organizations. It allows you to easily define automated rules called constraints. Among them, default constraint allows you to automatically set default values for MySQL columns, when new rows are created in a table. However, sometimes you may need to remove default constraint or drop default value in MySQL. In this article, we will look at how to remove default value of column in MySQL.

Why Default Values Are Important

Default value settings allow databases to automatically assign values to columns, even if no values are explicitly specified for them in INSERT statement. It provides many benefits such as:

1. Simplified data entry – It allows you to easily enter data automatically without actually specifying it in SQL queries. Even if you forget to mention the column in your INSERT query, MySQL will automatically populate it with default value. This avoids errors due to missing values. It is commonly used in transactional databases to record sign up, sale and order dates.
2. Maintain Data Integrity – Since columns with default values are always populated, whether they are explicitly mentioned or not, they are never blank. This constraint is really useful for columns that are foreign keys to maintain data integrity.

How to Remove Default Value of Column in MySQL

It is very easy to remove default constraint in MySQL. We will use ALTER TABLE statement with DROP DEFAULT clause to remove default value of column in MySQL.

Here is the syntax to drop default constraint in MySQL

mysql> ALTER TABLE table_name           
       ALTER column_name DROP DEFAULT;

In the above query, you need to specify the table name and column name for which you want to remove default value.

Also read : How to Add Default Constraint in MySQL

Here is an example to remove default constraint. Let us say you have the following database table where city column has default value equal to NYC’.

CREATE TABLE employees (
    id int NOT NULL,
    name varchar(255) NOT NULL,
    age int,
    city varchar(255) DEFAULT 'NYC'
);

You can verify the constraint using SHOW TABLE or DESCRIBE TABLE statement.

mysql> describe employees;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(255) | NO   |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| city  | varchar(255) | YES  |     | NYC     |       |
+-------+--------------+------+-----+---------+-------+

Here is the SQL query to drop default constraint in MySQL.

mysql> ALTER TABLE employees       
       ALTER city DROP DEFAULT; 

When you drop DEFAULT constraint, the column’s default value will revert to the standard default value for the column type. For example, for those columns that can contain null values, it will be NULL. Otherwise, it will be 0, empty string, or ‘0000-00-00’ for numeric, string and date/time/datetime columns respectively. For columns having AUTO_INCREMENT constraint such as primary key columns, the default value will be sequentially next number.

Similarly, you can also remove NOT NULL constraint in MySQL.

If you do not want to completely drop the default constraint but only change the default value, then you can use the following statement.

mysql> ALTER TABLE employees
ALTER city SET DEFAULT 'San Francisco';

You can verify the changes using SHOW TABLE or DESCRIBE TABLE statement.

mysql> describe employees;
+-------+--------------+------+-----+---------------+-------+
| Field | Type         | Null | Key | Default       | Extra |
+-------+--------------+------+-----+---------------+-------+
| id    | int(11)      | NO   |     | NULL          |       |
| name  | varchar(255) | NO   |     | NULL          |       |
| age   | int(11)      | YES  |     | NULL          |       |
| city  | varchar(255) | YES  |     | San Francisco |       |
+-------+--------------+------+-----+---------------+-------+

Please note, you do not need to drop default constraint in order to change it. Also, it will not update any of the existing column values, but only future values.

Conclusion

It is important to note, that when you remove default value constraint from a column or change it, this will not update any of the existing rows. It will only disable assignment of default values in future INSERT statements. Also, it is important to analyze the implications of removing default constraint especially if it is foreign key or relationship.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!