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

Suppose you have an existing table T and want to add a new type 'text' column named C that will be NOT NULL and have no default value. If table T already contains rows (or, on some DBMS's, even if the table is empty), you cannot add a NOT NULL column without providing a value for every row. There are two potential solutions:

  1. Add the column with a default value and then, after it is created, remove the default value property. This does not work for column types that do not allow default values at all (such as 'text' and 'blob' on MySQL).
  2. Add the column without NOT NULL so all rows get a NULL value, UPDATE the column to set a value for all rows, then add the NOT NULL property to the column. This works for all column types.

The Schema API db_add_field() function supports option 2. The function accepts a field specification array to add to a table. The specification may contain the parameter 'initial' and the newly created field will be set to the value of the key in all rows. db_add_field() creates the column as NULLable, performs the UPDATE statement and then, if appropriate, sets it to NOT NULL. The 'initial' parameter works for all columns but is the only way to create NOT NULL columns with no default value in existing tables.

For example, to add a NOT NULL type 'text' column col1 with no default value to the table T and initialize it to the string 'start' in all rows:

$ret = array();
db_add_field($ret, 'T', 'col1', array('type' => 'text',
                                      'not null' => TRUE,
                                      'initial' => 'start'));

It is important to understand that the 'initial' parameter only has effect when the column is first added. In this example, all subsequent INSERT INTO statements for this table must provide a value for col1.

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