Column DEFAULT
values can play an important role in maintaining data integrity. In the event no value is provided for DML operations, the DEFAULT
constraint can ‘pick up the slack’. While MySQL does support the DEFAULT
column constraint, there are some nuances to be aware of. Want to explore and learn them with me? Let’s go!

Photo by John Moeses Bauan on Unsplash
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
OS and DB used:
- Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
- MySQL 5.7.23
As of late, I have been studying, exploring, and blogging about constraints within MySQL, so be sure and visit these accompanying blog posts I have written so far:
- FOREIGN KEYS in MySQL with examples.
- FOREIGN KEY Referential Actions in MySQL – with examples.
- Exploring MySQL Constraints: NOT NULL – With examples.
What is a DEFAULT
constraint?
With a DEFAULT
constraint in place on a column, should no value be provided for that column during INSERT
or UPDATE
statements, the value specified in the DEFAULT
clause definition will be used in said operation.
Let’s establish an arbitrary example table where all columns have the DEFAULT
constraint applied:
1 2 3 4 | mysql> CREATE TABLE trial( id INTEGER DEFAULT 10, -> col_a VARCHAR(20) DEFAULT 'Hardy', -> col_c NUMERIC(4,2) DEFAULT 22.22); Query OK, 0 rows affected (0.49 sec) |
Then, issue an INSERT
with those defaults and query for the results:
1 2 3 4 5 6 7 8 9 10 | mysql> INSERT INTO trial VALUES(); Query OK, 1 row affected (0.06 sec) mysql> SELECT * FROM trial; +------+-------+-------+ | id | col_a | col_c | +------+-------+-------+ | 10 | Hardy | 22.22 | +------+-------+-------+ 1 row in set (0.00 sec) |
The query results reflect that the DEFAULT
values for each column were inserted because none were supplied in the VALUES
clause. This is how MySQL handles inserting explicit DEFAULT
values, or those you the DBA/Developer defines in the clause.
However, as you will see, the handling of implicit DEFAULT
values are a different story altogether.
Next, I create another table, with no DEFAULT
value for any columns. But, do note that column id_2
has the NOT NULL
constraint applied:
1 2 3 4 5 | mysql> CREATE TABLE trial_2( id_2 INTEGER NOT NULL, -> col_2 VARCHAR(20), -> col_3 NUMERIC(4,2), -> col_4 INTEGER); Query OK, 0 rows affected (0.36 sec) |
Again, I will run an INSERT
using defaults:
1 2 | mysql> INSERT INTO trial_2 VALUES(); ERROR 1364 (HY000): Field 'id_2' doesn't have a default value |
Let’s try again, but include an actual value for the id_2
column mentioned in the above error:
1 2 3 | mysql> INSERT INTO trial_2(id_2, col_2, col_3, col_4) -> VALUES(7, DEFAULT, DEFAULT, DEFAULT); Query OK, 1 row affected (0.04 sec) |
No error during that INSERT
. But what values were inserted?
1 2 3 4 5 6 7 | mysql> SELECT * FROM trial_2; +------+-------+-------+-------+ | id_2 | col_2 | col_3 | col_4 | +------+-------+-------+-------+ | 7 | NULL | NULL | NULL | +------+-------+-------+-------+ 1 row in set (0.00 sec) |
NULL
.
For better understanding, I’ll consult passages from the documentation to know what happens behind the scenes, most of which is dependent on the current Server SQL mode[1].
Be sure and visit the referenced documentation for exact verbiage and wording, as below, I provide my own high-level overview.
- If the column allows
NULL
values, an explicitDEFAULT NULL
is defined. - If
NULL
is not allowed in the column then MySQL does not define aDEFAULT
value for it. (See the referencing documentation for exceptions withPRIMARY KEY
columns.) - A
NOT NULL
column accepting data with no explicitDEFAULT
depends heavily on the active SQL Mode. For inserted data, the column is set toNULL
causing these SQL mode actions (explanations/wording included below are directly from the documentation): 1)
If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.
2)
If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.
DEFAULT
Caveat
Take this CREATE TABLE
statement, in which I define a column to have the CURRENT_DATE()
function for the DEFAULT
value:
1 2 3 | mysql> CREATE TABLE a_date(id INTEGER NOT NULL, -> prime_date DATE DEFAULT CURRENT_DATE()); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_DATE())' at line 2 |
This passage from section 11.7 Data Type Default Values[2] in the documentation clearly explains it for us:
With one exception, the default value specified in a DEFAULT clause must be a literal constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE.
Not to worry, for you can change the column data type slightly and accomplish more or less the same:
1 2 3 | mysql> CREATE TABLE a_date(id INTEGER NOT NULL, -> prime_date DATETIME DEFAULT CURRENT_TIMESTAMP()); Query OK, 0 rows affected (0.22 sec) |
Let’s see how this works with an INSERT
using the CURRENT_TIMESTAMP()
for the DEFALUT
value then query for the table data:
1 2 3 4 5 6 7 8 9 10 | mysql> INSERT INTO a_date(id, prime_date) VALUES(10, DEFAULT); Query OK, 1 row affected (0.09 sec) mysql> SELECT * FROM a_date; +----+---------------------+ | id | prime_date | +----+---------------------+ | 10 | 2018-09-24 06:16:40 | +----+---------------------+ 1 row in set (0.00 sec) |
Works no problem using CURRENT_TIMESTAMP()
.
DEFAULT
constraints are applied for UPDATE
operations as well.
See with the below example.
This row is present in table trial
:
1 2 3 4 5 6 7 8 9 | mysql> SELECT * -> FROM trial -> WHERE id = 22; +------+-------+-------+ | id | col_a | col_c | +------+-------+-------+ | 22 | Mary | 44.22 | +------+-------+-------+ 1 row in set (0.00 sec) |
Here I UPDATE
using the DEFAULT
keyword (and ultimately, value):
1 2 3 4 5 | mysql> UPDATE trial -> SET id = DEFAULT -> WHERE id = 22; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
Reflected change is visible in the id
column for the updated row:
1 2 3 4 5 6 7 8 | mysql> SELECT * FROM trial; +------+-------+-------+ | id | col_a | col_c | +------+-------+-------+ | 10 | Hardy | 22.22 | | 10 | Mary | 44.22 | +------+-------+-------+ 2 rows in set (0.00 sec) |
Data Types Not Supported
As of the time of this writing, the DEFAULT
constraint is not supported for the following data types:
BLOB
TEXT
GEOMETRY
JSON
Referencing Links And Supporting Documentation
For continued study and reading, be sure and visit these referencing links from the official MySQL documentation:
The DEFAULT
constraint provides at least some measure of control over the data integrity of your tables, ensuring those empty DML commands are accounted for.
Like what you have read? See anything incorrect? Please share your thoughts and comments below. Thanks for reading!!!
Explore the official MySQL 5.7 Online Manual for more information.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
One thought on “DEFAULT Constraint in MySQL with examples.”