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:
CREATE TABLE `vehicle` ( `id` int(11) NOT NULL AUTO_INCREMENT, `registration` varchar(8) NOT NULL, `make` varchar(255) NOT NULL, `model` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY (`registration`) )
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
Now if I was to run a query such as...
INSERT INTO `vehicle` (`registration`) VALUES ('LSO7VSC');
... 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).
Add the following line to your database configuration (usually at
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