MySQL DEFAULT Constraint

The MySQL DEFAULT constraint returns the default value for a table column. The DEFAULT value of a column is a value used in the case, when there is no value specified by the user.

To use this function there should be a DEFAULT value assigned to the column. Otherwise, it will generate an error.

Syntax

DEFAULT (column_name)

Where, column_name: Name of column whose default value is written.

How to Add DEFAULT CONSTRAINT

To add a DEFAULT constraint to a MySQL table, there are two methods available.

• Using CREATE TABLE Statement
• Using ALTER TABLE Statement

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

Using DEFAULT Constraint on CREATE TABLE

We can add a DEFAULT constraint while creating a table in MySQL.

Syntax

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

Example

CREATE TABLE customers (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) DEFAULT 'example@domain.com',
  city VARCHAR(50) DEFAULT 'Unknown'
);

Add DEFAULT Constraint with ALTER TABLE Statement

We can add DEFAULT Constraint to a already existing table using ALTER TABLE Statement.

Syntax

ALTER TABLE table_name ALTER column_name SET DEFAULT default_value;

Example

ALTER TABLE customers ALTER city SET DEFAULT 'New York';

DEFAULT Constraint Example

Let’s create a MySQL table “blog_posts”

CREATE TABLE blog_posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    published_date DATE DEFAULT CURRENT_DATE
);

Final Table:

Field	Type	Null	Key	Default	Extra
post_id	int	NO	PRI	NULL	auto_increment
title	varchar(255)	NO		NULL
content	text	NO		NULL
published_date	date	YES		CURRENT_DATE

In this table, we have set the publish_date value to default value of the current date using CURRENT_DATE. So if a new entry doesn’t provide value for publish_date column, MySQL will automatically take the current date.

Let’s try and insert new values into this table.

INSERT INTO blog_posts (title, content) VALUES ('My First Blog Post', 'Hello, world! This is my inaugural post.');

Output:

post_id	title	content	published_date
1	My First Blog Post	Hello, world! This is my inaugural post.	2023-03-12

Key TakeAways:

1. Compatibility with Data Types: The default value must be compatible with the column’s data type.
2. Expression Defaults (MySQL 8.0.13+): Starting from MySQL 8.0.13, you can use expressions as default values.
3. Limitations on BLOB and TEXT Columns: Prior to MySQL 8.0.13, BLOB and TEXT columns cannot have a DEFAULT value due to their nature of storing large amounts of data. However, from MySQL 8.0.13 and onward, this restriction has been lifted for certain expressions like CURRENT_TIMESTAMP.
4. NOT NULL Columns: If a column is set to NOT NULL without a DEFAULT value explicitly defined, and you try to insert a row without specifying a value for this column, MySQL will throw an error. Defining a DEFAULT value can prevent such errors.