Sometimes you may need to add default value to column or add column with default value in MySQL. You can easily do this using default constraint in MySQL. Here’s how to add default constraint in MySQL.
How To Add Default Constraint in MySQL
Here are the steps to add default constraint in MySQL. You can add default constraint while creating table using CREATE TABLE statement, or after table creation using ALTER TABLE statement. We will look at both these approaches.
How to Add Default Constraint using CREATE TABLE
Here’s the syntax to add default constraint using CREATE table statement.
mysql> create table table_name(
column1 column1_definition DEFAULT default_value,
column2 column2_definition,
...
);
In the above SQL query, you need to specify the table name table_name. Also, you need to mention DEFAULT after the column definition of the field for which you want to add default value. You need to also specify the default value of your column.
Bonus Read : MySQL Select Top N Rows Per Group
Here’s an example to add default constraint in MySQL.
mysql> create table sample_data(
id int,
order_date date,
amount int DEFAULT 0
);
mysql> insert into sample_data(id, order_date)
values(1,'2020-08-01');
mysql> select * from sample_data;
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 1 | 2020-08-01 | 0 |
+------+------------+--------+
In the above SQL query, we create sample_data table with DEFAULT value 0 for column amount. As you can see, when we insert values into sample_data, we do not mention the value for amount column. Yet, MySQL automatically assigns the default value 0 to this column.
Bonus Read : How to Get Last Week Data in MySQL
ALTER TABLE add column with default value in MySQL
Similarly, you can add default constraint using ALTER TABLE statement for existing tables. Here’s the syntax for it
mysql> ALTER TABLE table_name
ALTER column_name SET DEFAULT default_value;
In the above ALTER TABLE statement you need to mention table_name, column_name and default_value
Here’s an example to add default constraint using ALTER TABLE. Let’s say you want to set default value for order_date column in above table sample_data
mysql> ALTER TABLE sample_data
ALTER order_date SET DEFAULT '2020-08-03';
mysql> insert into sample_data(id)
values(2);
mysql>select * from sample_data;
mysql> select * from sample_data;
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 1 | 2020-08-01 | 0 |
| 2 | 2020-08-03 | 0 |
+------+------------+--------+
In the above SQL query, we have added default constraint to order_date column. After that, we provide only id column’s value in our INSERT statement. Yet, MySQL automatically populates order_date and amount columns based on their default values.
Bonus Read : MySQL Copy Database
How to Remove Default Value to Column
If you need to remove default value for column, then use DROP DEFAULT statement. Here’s the syntax for DROP DEFAULT query.
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 drop DEFAULT constraint.
Here’s an example of DROP DEFAULT constraint in SQL.
mysql> ALTER TABLE sample_data
ALTER order_date DROP DEFAULT;
In the above statement, we have removed default constraint from order_date column.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
