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

"Standard" SQL specifies the following rules:

  1. For each row in table, a column can contain a value or NULL which indicates "no value."
  2. If a column is declared NOT NULL, it must always contain a non-NULL value; NULL is not allowed.
  3. Columns can have a default value.

When inserting a row without providing an explicit value for a column, that column will get the default value if the column has one or NULL if the column is not specified as NOT NULL. Otherwise, an error is generated.

For example, consider the following table definition and matching CREATE TABLE statement:

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

  i1 integer,
  i2 integer NOT NULL,
  i3 integer NOT NULL DEFAULT 17

Column i1 can be an integer or NULL. Column i2 can only be integer, not NULL, and a value MUST be provided for every row. Column i3 can only be an integer, not NULL, but if a value is not provided for a row the value 17 is used instead.

This table definition controls what kind of INSERT statements are legal:

INSERT INTO T (i1, i2, i3) VALUES (0, 0, 0)	-- okay
INSERT INTO T (i2, i3) VALUES (0, 0)		-- okay; i1 is NULL
INSERT INTO T (i1, i2) VALUES (0, 0)		-- okay; i3 is 17
INSERT INTO T (i1) VALUES (0)			-- error; no value for i2

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