Create a DEFAULT Constraint in MySQL

In MySQL, a DEFAULT constraint is used to provide a default value for a column when no value is specified during an INSERT operation. This is particularly useful for ensuring that important columns have consistent, non-null values, even when omitted from inserts.

In this article, we’ll use MySQL to create a table with a couple of DEFAULT constraints, and we’ll also add a constraint to that table after it has been created.

What Is a `DEFAULT` Constraint?

The DEFAULT constraint is a common type of constraint in SQL databases that automatically assigns a predefined value to a column if no explicit value is given in the INSERT statement when inserting a new row.

In MySQL, a DEFAULT constraint can be a literal constant or an expression, and it can be applied to most data types, like INT, VARCHAR, DATE, and others.

Why Use a `DEFAULT` Constraint?

Some reasons for using DEFAULT constraints include:

Ensuring that columns always have meaningful values.
Reducing NULL values in our tables.
Simplifying INSERT statements by allowing us to omit certain columns.

Syntax for `DEFAULT` Constraints

Here is the basic syntax for adding a DEFAULT constraint when creating a new table:

CREATE TABLE table_name (
    column_name1 data_type DEFAULT default_value,
    column_name2 data_type DEFAULT default_value,
    ...
);

You can also modify an existing table to add a DEFAULT constraint using the ALTER TABLE statement:

ALTER TABLE table_name
MODIFY column_name data_type DEFAULT default_value;

Expressions

You can provide an expression as the default value. If so, it must be enclosed in parentheses (unless the default value is the CURRENT_TIMESTAMP function).

Therefore, the basic syntax for providing an expression goes like this:

CREATE TABLE table_name (
    column_name1 data_type DEFAULT (default_expression),
    column_name2 data_type DEFAULT (default_expression),
    ...
);

Create a Table with `DEFAULT` Values

Let’s create a table called players where the status column has a default value of 'active', and the created_at column has a default value of the current timestamp:

CREATE TABLE players (
    player_id INT AUTO_INCREMENT PRIMARY KEY,
    player_name VARCHAR(50) NOT NULL,
    status VARCHAR(10) DEFAULT 'active',
    points INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Here, if we insert a new user without specifying status, the database will automatically set it to 'active'. Similarly, created_at will default to the current time if not provided. As mentioned, we don’t need to provide parentheses around the CURRENT_TIMESTAMP function, whereas with other expressions we would need to surround the expression with parentheses.

Let’s go ahead and insert a row into the players table without providing values for status or created_at:

-- Insert data
INSERT INTO players (player_name, points) VALUES ('Fitch', 10);

-- Select data
SELECT * FROM players;

Output:

+-----------+-------------+--------+--------+---------------------+
| player_id | player_name | status | points | created_at          |
+-----------+-------------+--------+--------+---------------------+
|         1 | Fitch       | active |     10 | 2024-09-28 06:28:52 |
+-----------+-------------+--------+--------+---------------------+

I only provided a value for two columns, but because I had created DEFAULT constraints on two other columns (status and created_at), these columns were populated with their respective default values. As for the player_id column, this is automatically populated due to it being an AUTO_INCREMENT column (not a DEFAULT constraint).

Add a `DEFAULT` Constraint to an Existing Column

Suppose we forgot to add a DEFAULT constraint when we initially created the table. We can modify the existing table to add the default value using the ALTER TABLE statement.

ALTER TABLE players
MODIFY COLUMN points INT DEFAULT 0;

Now, the default value for the points column is 0 for any new rows where the points is not provided.

Let’s insert another row, but this time we’ll omit a value for the points column:

-- Insert data
INSERT INTO players (player_name) VALUES ('Bec');

-- Select data
SELECT * FROM players;

Output:

+-----------+-------------+--------+--------+---------------------+
| player_id | player_name | status | points | created_at          |
+-----------+-------------+--------+--------+---------------------+
|         1 | Fitch       | active |     10 | 2024-09-28 06:28:52 |
|         2 | Bec         | active |      0 | 2024-09-28 06:33:13 |
+-----------+-------------+--------+--------+---------------------+

As expected, the points column’s value is 0 for the new row.

Remove a `DEFAULT` Constraint

We can remove a DEFAULT constraint by using DROP DEFAULT in an ALTER TABLE ... ALTER COLUMN statement:

ALTER TABLE players
ALTER COLUMN status DROP DEFAULT;

This example removes the DEFAULT constraint from the status column.

What Is a DEFAULT Constraint?

Why Use a DEFAULT Constraint?

Syntax for DEFAULT Constraints