Last updated 19 February 2008. Created on 14 July 2007.
Edited by ax, bjaspan. Log in to edit this page.

[Note: the following only applies when MySQL (5) is not running in Strict Mode. Every developer is encouraged to enable Strict Mode to produce standard conformant SQL and avoid sloppy coding.]

MySQL does not enforce the "standard" SQL rules for NOT NULL and default values on columns. It gives all columns an "implicit default value" even if no default value is specified for the column, making it possible to insert rows without providing a value for a NOT NULL, no default column. For example, consider this table:

$schema['T'] = array(
  'fields' => array(
    'i1' => array('type' => 'int'),
    'i2' => array('type' => 'int', 'not null' => TRUE),

In "standard" SQL, the statement INSERT INTO T (i1) VALUES (NULL) is illegal because no value is provided for the NOT NULL column i2. In MySQL, it is legal because i2 gets the "implicit default value" for integers of 0. This behavior is not portable to other database systems and code that relies on it is flawed and considered sloppy. Unfortunately, because so many Drupal and PHP programmers learned SQL using MySQL, INSERT queries that depend on this behavior often occur.

When porting code that relies on MySQL implicit default values to other databases, it is tempting to "fix the problem" by indiscriminately adding a default value to all columns, thus mimicking MySQL's sloppy behavior. This practice is discouraged. There is nothing wrong with default values, but columns should only have a default value when it makes semantic sense for that particular table. Instead of adding an illogical default value to a column, just fix the INSERT queries for that table by providing values for all columns. In the example above, change the query to read INSERT INTO T (i1, i2) VALUES (NULL, 0).

Looking for support? Visit the forums, or join #drupal-support in IRC.