MySQL DEFAULT Constraint

The DEFAULT constraint in MySQL provides the default value for a column in a table if no other value is specified. To use this feature a default value should be assigned to the column while creating a table or altering the table. Otherwise, it will generate an error.

MySQL DEFAULT Constraint Syntax

There are two methods available to implement MySQL DEFAULT constraint.

1. Using CREATE TABLE statement.
2. Using ALTER TABLE statement.

We can use these methods depending on whether we are creating a table or updating an existing one.

1) DEFAULT constraint using CREATE TABLE Statement

Below is the syntax of the DEFAULT constraint while creating a table in MySQL.

CREATE TABLE table_name (
	column1 datatype DEFAULT default_value,
	column2 datatype DEFAULT default_value,
	......
);

2) DEFAULT constraint using ALTER TABLE Statement

We can add a default constraint to an already existing table using the ALTER TABLE statement.

ALTER TABLE table_name
ALTER column_name SET DEFAULT default_value;

MySQL DEFAULT Constraint Example

Let’s create MySQL table orders.

CREATE TABLE orders (
	orderid INT AUTO_INCREMENT PRIMARY KEY,
	item varchar(50) NOT NULL,
	amount DECIMAL(10,2),
	order_date DATE DEFAULT (CURRENT_DATE)
);

In this table, we have defined order_date value to the default value current date using CURRENT_DATE. So, if order_date value is not provided MySQL will insert the current date as the default value into the orders table.

Now let’s, try to insert a new value into the table.

INSERT INTO orders(orderid,item,amount) VALUES
(1,'Keyboard','550');

Output:-

mysql> SELECT * FROM orders;
+---------+----------+--------+------------+
| orderid | item     | amount | order_date |
+---------+----------+--------+------------+
|       1 | Keyboard | 550.00 | 2024-10-06 |
+---------+----------+--------+------------+
1 row in set (0.10 sec)