DEFAULT Constraint in MySQL with examples.

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!

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:

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 explicit DEFAULT NULL is defined.
  • If NULL is not allowed in the column then MySQL does not define a DEFAULT value for it. (See the referencing documentation for exceptions with PRIMARY KEY columns.)
  • A NOT NULL column accepting data with no explicit DEFAULT depends heavily on the active SQL Mode. For inserted data, the column is set to NULL 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.”

  1. Pingback: Understanding the UNIQUE constraint in MySQL – with examples. | Digital Owl's Prose

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.