Last updated February 19, 2008. Created by Pancho on November 8, 2007.
Edited by ax, bjaspan. Log in to edit this page.
As documented in http://drupal.org/node/146939, type 'text' columns cannot have default values. This restriction is imposed by MySQL. However, as described at http://drupal.org/node/159330, MySQL treats text columns as if they have an implicit default value of the empty string. These two behaviors encourage non-portable programming practices but it is easy to avoid those practices and do things correctly.
Suppose you have the following table definition:
<?php
$schema['mytable'] = array(
'fields' => array(
'id' => array('type' => 'int'),
'info' => array('type' => 'text', 'not null' => TRUE)),
);
?>The 'info' column is type text. It is not null and, as is required, has no default value. To write rows to this table, you always need to provide a value for the 'info' column even if it is the empty string. Here are correct examples:
<?php
$row->id = $whatever;
$row->info = '';
// using an explicit INSERT statement:
db_query("INSERT INTO {mytable} (id, info) VALUES (%d, '%s')", $row->id, $row->info);
// using drupal_write_record():
drupal_write_record('mytable', $row);
?>You cannot perform these inserts without a value for 'info'. Here are incorrect examples:
<?php
$row->id = $whatever;
// $rows->info is not defined!
// INCORRECT explicit INSERT statement:
db_query("INSERT INTO {mytable} (id) VALUES (%d)", $row->id);
// INCORRECT usage of db_write_record():
drupal_write_record('mytable', $row);
?>Both of the incorrect examples will work on MySQL but will fail on PostgreSQL and any other database system that enforces normal rules for not null, no default columns.
You may be tempted to solve the problem by changing your table declaration to provide a default value of the empty string for the info column. DON'T! Again, doing so will appear to work but in fact will result in a database table that does not exactly match your schema definition. Instead, always provide a value for all text columns in all INSERT statements.