How To Add Default Constraint in MySQL

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!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.