MySQL DEFAULT Constraint (original) (raw)

Last Updated : 18 Mar, 2024

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:

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 INTOblog_posts (title, content) VALUES ('My First Blog Post', 'Hello, world! This is my inaugural post.');

**Output:

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.