MySQL Default

“In this post, we will discuss how to use the DEFAULT constraint in the MySQL column. We will learn how to use this constraint type in the table schema definition for a specific column.”

If that sounds interesting, let us dive and learn more.

MySQL DEFAULT Constraint Syntax

The snippet below shows the syntax of the DEFAULT constraint:

col_name data_type DEFAULT default_value;

Let us explore the syntax above.

We start with the column_name, which specifies the name of the column on which you wish to set a default constraint. We then define the data type for the said column.

The DEFAULT keyword is followed by the default value, which defines a literal value. Hence, if you insert or update a row that does not include a value for that column, MySQL will use the defined default value instead of a NULL type.

It is good to keep in mind that the default value cannot be an expression or a function.

It is a good practice to define the default constraint during table definition. This helps to preserve the data in the table, and no prior data is set to NULL.

Practical Example

The following example shows how to set the default constraint during table creation.

mysql> create database zero_day;
Query OK, 1 row affected (0.00 sec)

Use target database:

mysql> use zero_day;
Database changed

Create a table with the schema shown below:

mysql> create table records (
    ->     id int not null auto_increment primary key,
    ->     report_name varchar(255) not null,
    ->     report_date date not null,
    ->     report_author varchar(100) default 'anonymous',
    ->     severity int not null,
    ->     check(severity > 0)
    -> );

The above query creates a table called_records with various columns. Pay attention to the report_author column. We define a default constraint and set the default value to “anonymous”.

Hence, if we do not specify the value for that column, MySQL will use that value instead of NULL.

You can confirm the table schema after the fact using the desc command:

DESC records;

This should return the table definition as a table.

As you can see, the report_author contains a default value while the rest of the columns use NULL as the default value.

To test this, we can insert the records as shown:

insert into records(report_name, report_date, severity)
values ('DESC-100', '2022-01-20', 6);

The insert statement above adds one record to the table without providing the value for the reports_table.

This should force MySQL to use the default value as shown:

mysql> select * from records;

The output table:

| id | report_name | report_date | report_author | severity |
|  1 | DESC-100    | 2022-01-20  | anonymous     |        6 |
1 row in set (0.00 sec)

As we can see, the table contains the value “anonymous” instead of NULL.

Add Default Constraint on Existing Table

We can also add a default constraint to an existing table using the ALTER TABLE command as shown:

The command syntax is as shown:

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

For example, to add a default constraint to the severity column:

mysql> ALTER TABLE records ALTER COLUMN severity SET DEFAULT 5;

This should update the severity column to include the default constraint with a value of 5.


In this post, we discussed how to use the set and use the default constraint in a table column.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list