Programster's Blog

Tutorials focusing on Linux, programming, and open-source

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.

Last updated: 25th January 2021
First published: 16th August 2018

This blog is created by Stuart Page

I'm a freelance web developer and technology consultant based in Surrey, UK, with over 10 years experience in web development, DevOps, Linux Administration, and IT solutions.

Need support with your infrastructure or web services?

Get in touch