MySQL - Prevent Defaulting to Allow Defaults
In a fresh installation of MySQL 5.6 on Ubuntu (and probably many other distributions) the sql_mode
is not specified by default. This results in a "loose" mode of operation such as described below.
Consider the following table definition:
This is based on every car having a unique registration and every car has a make and model, such as a Volkswagen Golf. I do not wish to have empty values, hence I did not specify a DEFAULT
, and I also set NOT NULL
.
Now if I was to run a query such as...
... you might expect it to fail, but the default behaviour is that it will actually get inserted and your table will now have the following data:
+----+--------------+------+-------+
| id | registration | make | model |
+----+--------------+------+-------+
| 1 | LSO7VSC | | |
+----+--------------+------+-------+
By default, columns that cannot take a null value, will default to an empty string, 0
, or the current timestamp etc. If they can take a null value, then they will default to null. This can get interesting with replace statements.
I would argue that if I wanted to allow a default value of 0 or an empty string, I would have explicitly stated so in my table definition with DEFAULT 0
, or DEFAULT ""
. What we really want from such an insert statement is an error message, such as:
ERROR 1364 (HY000): Field 'make' doesn't have a default value
This prevents mistakes and letting applications enter incomplete data into the database. I would argue that one of the greatest benefits to a database is that it can enforce a structure that you can rely upon later and not worry about incomplete data. Otherwise you will be spending a good portion of your time writing handler code to manage these inconsistencies. If you wanted to operate without such a stringent operation, then you could specify the appropriate default values in the table definition (sometimes 0 or an empty string is not appropriate and really shouldn't be assumed).
The Solution
Add the following line to your database configuration (usually at /etc/mysql/my.cnf
).
The key thing here is that STRICT_TRANS_TABLES
will result in throwing an error if someone provides "incomplete" row data.
First published: 16th August 2018