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 NOT NULL.

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).

The Solution

Add the following line to your database configuration (usually at /etc/mysql/my.cnf).

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

The key thing here is that STRICT_TRANS_TABLES will result in throwing an error if someone provides "incomplete" row data.