Updating tables: hook_update_N() functions

Last updated November 2, 2011. Created by snufkin on June 7, 2007.
Edited by fmitchell, drclaw, jhodgdon, kiamlaluno. Log in to edit this page.

As in previous versions of Drupal, you can update database tables for new versions using a hook_update_N() function.

Adding a new column (D6)

Suppose that mymodule adds a new column called 'newcol' to mytable1 in version 6.x-1.5. Prior to Schema API, you would:

1. Add newcol to the CREATE TABLE statements in mymodule_install().
2. Create a mymodule_update_N() function to add newcol to existing mytable1 tables with ALTER TABLE statements.

Using Schema API, you perform the same two steps:

1. Add newcol to the table definition array in mymodule_schema() in mymodule.install.
2. Create a mymodule_update_N() function to add newcol to existing mytable1 tables with the Schema API function db_add_field():
   <?php
function mymodule_update_6100() {
  $ret = array();
  db_add_field($ret, 'mytable1', 'newcol', array('type' => 'int', 'not null' => TRUE));
  return $ret;
}
?>

Adding a new column (D7)

Using Schema API, you perform the same two steps:

1. Add newcol to the table definition array in mymodule_schema() in mymodule.install.
2. Create a mymodule_update_N() function to add newcol to existing mytable1 tables with the Schema API function db_add_field():
   <?php
function mymodule_update_7100() {
  $spec = array(
    'type' => 'varchar',
    'description' => "New Col",
    'length' => 20,
    'not null' => FALSE,
  );
  db_add_field( 'mytable1', 'newcol', $spec);
}
?>

Adding a new column to a custom field (D7)

Using Field API, you've already created a new field and it works great. You want to add a new form element to that field.

The original schema, using hook_field_schema():

To add a new column, do the following two steps:

1. Add newcol to the table definition array in mymodule_field_schema() in mymodule.install. When you do this, the field configuration is automatically updated as field_read_fields invokes the updated schema in the install file.
2. Create a mymodule_update_N() function to add newcol to existing mytable1 tables with the Schema API function db_add_field(), but also add it to the revision table:
   <?php
function mymodule_update_7100(&$sandbox) {
  $spec = array(
    'type' => 'varchar',
    'description' => "New Col",
    'length' => 20,
    'not null' => FALSE,
  );
  $data_table_name = 'field_data_field_myfield';
  $revision_table_name = 'field_revision_field_myfield';
  $field_name = 'field_myfield_newfield';

  db_add_field($data_table_name, $field_name, $spec);
  db_add_field($revision_table_name, $field_name, $spec);
}
?>

Adding a new table

Similarly, suppose that for version 6.x-1.6 mymodule now needs a completely new table called mytable2. You perform the same two steps:

1. Add the new table to mymodule_schema() in mymodule.install.
2. Create a mymodule_update_N() function to create mytable 2 with the Schema API function db_create_table():
   <?php
function mymodule_update_6101() {
  $schema['mytable2'] = array(
     // table definition array goes here
  );
  $ret = array();
  db_create_table($ret, 'mytable2', $schema['mytable2']);
  return $ret;
}
?>

Adding keys

And as for adding a unique, or a primary key, one can now use dedicated API functions:

Important note: You may be tempted to pass a table definition from your own hook_schema function directly to db_create_table(). Please read why you cannot use hook_schema from within hook_update_N().

Updating a Table

This simple example shows how to change a modules weight using an sql UPDATE with the function update_sql()