“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:
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.
Query OK, 1 row affected (0.00 sec)
Use target database:
Database changed
Create a table with the schema shown below:
-> 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:
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:
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:
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 COLUMN column_name SET DEFAULT default_value;
For example, to add a default constraint to the severity column:
This should update the severity column to include the default constraint with a value of 5.
Conclusion
In this post, we discussed how to use the set and use the default constraint in a table column.
