Last updated July 6, 2013. Created by CitizenKane on May 25, 2007.
Edited by chx, vovtz, tstivers, wouter99999. Log in to edit this page.

A Drupal schema definition is an array structure representing one or more tables and their related keys and indexes. A schema is defined by hook_schema(), which must live in the modulename.install file. hook_schema() should return an array mapping 'tablename' => array(table definition) for each table that the module defines. The following keys in the table definition are processed during table creation:

  • 'description': A string describing this table and its purpose. References to other tables should be enclosed in curly-brackets. For example, the node_revisions table description field might contain "Stores per-revision title and body data for each {node}."
  • 'fields': An array mapping 'fieldname' => array(field definition) that describes the table's database columns. The specification is also an array. The following specification parameters are defined:
    • 'description': A string describing this field and its purpose. References to other tables should be enclosed in curly-brackets. For example, the node table vid field description might contain "Always holds the largest (most recent) {node_revisions}.vid value for this nid."
    • 'type': The generic datatype: 'varchar', 'char', 'int', 'serial', 'float', 'numeric', 'text', 'blob' or 'datetime'. The types map to the underlying database engine specific datatypes. Use 'serial' for auto incrementing fields.
    • 'mysql_type', 'pgsql_type', 'sqlite_type', etc: The database driver specific type. For example, 'mysql_type'  => 'TIME' is 'pgsql_type'  => 'time without time zone'.
    • 'size': The data size: 'tiny', 'small', 'medium', 'normal', 'big'. This is a hint about the largest value the field will store and determines which of the database engine specific datatypes will be used (e.g. on MySQL, TINYINT vs. INT vs. BIGINT). 'normal', the default, selects the base type (e.g. on MySQL, INT, VARCHAR, BLOB, etc.). Data Types and Sizes are explained here.
    • 'not null': If true, no NULL values will be allowed in this database column. Defaults to false.
    • 'default': The field's default value. The PHP type of the value matters: '', '0', and 0 are all different. If you specify '0' as the default value for a type 'int' field it will not work because '0' is a string containing the character "zero", not an integer.

      Note that type 'text' and 'blob' fields cannot have default values.

    • 'length': The maximal length of a type 'char', 'varchar' or 'text' field. Ignored for other field types. Note, length is required for 'varchar's.
    • 'unsigned': A boolean indicating whether a type 'int', 'float' and 'numeric' only is signed or unsigned. Defaults to FALSE. Ignored for other field types.
    • 'precision', 'scale': For type 'numeric' fields, indicates the precision (total number of significant digits) and scale (decimal digits right of the decimal point). Both values are mandatory. Ignored for other field types.
    • 'serialize': A boolean indicating whether the field will be stored as a serialized string

    All parameters apart from 'type' are optional except that type 'numeric' columns must specify 'precision' and 'scale', and 'varchar' columns must specify 'length'.

  • 'primary key': An array of one or more key column specifiers that form the primary key.
    • A key column specifier is either a string naming a field or an array of two elements, a string naming a field and an integer prefix length. If a prefix length is specified, only that many bytes or characters of the named field are used as part of the key. If the database engine does not support this optimization, the prefix length is ignored.
    • All fields listed in the primary key must have 'not null' => TRUE in their specification.
  • 'unique keys': An associative array of unique keys ('keyname' => specification). Each specification is an array of one or more key column specifiers (see above) that form a unique key on the table.
  • 'indexes': An associative array of indexes ('indexname' => specification). Each specification is an array of one or more key column specifiers (see above) that form an index on the table.
  • 'foreign keys': An associative array of foreign keys ('keyname' => specification). Each specification is an array with 'table' and 'columns' elements that form a foreign key for the table.
    'table' is a string specifying the foreign table, and 'columns' is an associative array in the format 'source_column' => 'target_column'. The source_column refers to a column in the table being defined right in this hook_schema implementation, while target_column refers to a column in the foreign table.
    Note: Foreign key definitions were added in Drupal 7 for documentation purposes only, and do not modify the database.
    Example from the {node} table:
       'foreign keys' => array(
          'node_revision' => array(
            'table' => 'node_revision',
            'columns' => array('vid' => 'vid'),
            ),
          'node_author' => array(
            'table' => 'users',
            'columns' => array('uid' => 'uid')
            ),
           ),
  • 'mysql_suffix': In MySQL databases, a string that is added as a suffix in the CREATE TABLE sentence. Drupal 6 only. In Drupal 7 use mysql_engine’, ‘mysql_character_set‘ and ‘collation’ . Example:
        'mysql_suffix' => " DEFAULT CHARACTER SET UTF8 ENGINE = INNODB AUTO_INCREMENT=3844 ",
  • 'mysql_engine': In MySQL databases, the engine to use instead of the default. In Drupal 6, use mysql_suffix instead. Example:
      'mysql_engine' => 'MyISAM',
  • 'mysql_character_set': In MySQL databases, the character set to use instead of the default. In Drupal 6, use mysql_suffix instead.
  • 'collation': In MySQL databases, the collation to use instead of the default. In Drupal 6, use mysql_suffix instead.

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

Comments

I might suggest that

All parameters apart from 'type' are optional except that type 'numeric' columns must specify 'precision' and 'scale'.

should be amended to also note that type 'varchar' columns must specify 'length'.

Note that there is no generic datatype 'bool'.
Instead you have to use something like type 'int' with size 'tiny' (aka tinyint in MySQL).

The schema module generates a field called 'disp-width' but it is not documented here?

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

"disp-width: The display width of non-varchar columns for the mysql command-line client. Only used by MySQL."
http://jaspan.com/schema-project-status-report

------------------------------------------
Drupal Specialists: Consulting, Development & Training

Robert Castelo, CTO
Code Positive
London, United Kingdom
----

In converting some modules to the Schema API I've noticed that developers tend to create indexes and primary keys on the same (sets of) columns. From the PostgreSQL documentation:

PostgreSQL automatically creates a unique index when a unique constraint or a primary key is defined for a table.

And the MySQL documentation:

Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees.

Thus it seems that a primary key implies an index, and to also declare the index is redundant (though perhaps not harmful). Perhaps the documentation should reflect this... the result might be cleaner module code.

Many, if not most, module maintainers are self-taught on PHP and and MySql, so any decent, low-level educational material would be really helpful across the board. Why not join the doc team and pitch in?

It seems this reference information is the same as the one shown on the Schema API documentation page but there's a small issue that can cause some confusion:

In that page the keys of the associative array for 'fields' are shown as list items at the same level than 'fields', 'primary key' and all other first level keys in the array. A little of CSS love could fix that to show it as it's shown in this page and make the idea clearer.

Cheers.

Alexis Bellido
Ventanazul: web development and Internet business consulting shouldn't be boring

I've discovered that you can't use a unique key on a field that's 'type' => 'text' (at least I can't with MySQL), you have to use 'type' => 'varchar' instead, I think a length is also necessary (but haven't tested it so cannot confirm).

Working code

  $schema['table_name'] = array(
    'description' => t('Description of table'),
    'fields' => array(
      'id' => array(
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'name' => array(
        'type' => 'varchar',
        'not null' => TRUE,
        'length'  => '50',
      ),
    'unique keys' => array(
      'name'     => array('name')
    ),
    'primary key' => array('id'),
  );

You want to use VARCHAR over TEXT where possible anyway (on MySQL).

A VARCHAR can be up to 65,535 bytes long and does not incur a performance hit like TEXT (or BLOB) fields do if you include them in a query that makes use of a temporary table (eg: a group by clause). If you do that with BLOB or TEXT, MySQL will *always* use a disk based temporary table, which is *slow*.

Instead, you can use a large VARCHAR and for instance limit the index size instead. For most names an index size of 3 chars will do just fine.

$schema['table_name'] = array(
  'description' => t('Description of table'),
  'fields' => array(
  'id' => array(
    'type' => 'serial',
    'unsigned' => TRUE,
    'not null' => TRUE,
    ),
  'name' => array(
    'type' => 'varchar',
    'not null' => TRUE,
    'length'  => '200',
    ),
  'indexes' => array(
    'name'     => array('name', 3)
  ),
  'primary key' => array('id'),
);

"A VARCHAR can be up to 65,535 bytes long..."

Mysql takes the length value as character length, therefore the maximum length for multibyte charsets has to be divided by the byte-length of the charset.
Fore example:
utf8 => max 3 bytes => max character length = 21845
utf8mb4 => max 4 bytes => max character length = 16383

The key 'mysql_suffix' can be used on the table definition to add mysql specific properties

for example

   'mysql_suffix' => " DEFAULT CHARACTER SET UTF8 ENGINE = INNODB AUTO_INCREMENT=3844 ",

See db_create_table_sql() in database.mysql-common.inc

Sean Burlington
www.practicalweb.co.uk

Thanks a lot for your comment. It is really useful.

I wonder why isn't this issue documented.

Tank you !!

I really don know how many time I saved, just on reading your post

'length' parameter is supported by MySQL for 'int' type, and the documentation as well mentions this,

'length': The maximal length of a type 'varchar', 'text' or 'int' field. Ignored for other field types.

But, it isn't working. I can confirm this problem on both Windows and CentOS with mysql version 5.1. I think the problem resides in the function _db_create_field_sql:

function _db_create_field_sql($name, $spec) {
  $sql = "`". $name ."` ". $spec['mysql_type'];
  if (in_array($spec['type'], array('varchar', 'char', 'text')) && isset($spec['length'])) {
    $sql .= '('. $spec['length'] .')';
  }

Clearly, 'length' parameter is ignored for the 'int' type. Including 'int' in the above array solved the problem.

Is this a documentation error or a bug? Where should I report this?

--
http://publicmind.in/blog
Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live - John F. Woods

It seems like a documentation error here, see: http://api.drupal.org/api/group/schemaapi/6. fixed.

--
http://publicmind.in/blog
Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live - John F. Woods

(In Drupal version 6.16) Key and Index column names aren't backquoted.

While column names in the main fields description are backquoted, column names in key and index definitions are are not.

If you create a table with 'awkward' column names, for example a name that is an SQL keyword, such as 'group', or with a hyphen in it like 'node-templates', that are not used as primary, unique key, or index values, the schema will build it just fine, as you would hope...

However, if you use an awkwardly named column as a key or index then the table construction will silently fail during installation of the schema, offering you no clue as to why the table wasn't added.

e.g. The following will create ok:

$schema['yourmodule_table'] = array(
'fields' => array(
'primaryKey' => array( 'type' => 'serial' ),
'group' => array( // This is not a unique key
  'type' => 'int', 'unsigned' => true, 'default' => 0, 'not null' => true ),
'someData' => array( 'type' => 'varchar', 'length' => 24 ),
),
'primary key' => array( 'primaryKey' )
);

But this will fail:

$schema['yourmodule_table'] = array(
'fields' => array(
'primaryKey' => array( 'type' => 'serial' ),
'group' => array( // This is not a unique key
  'type' => 'int', 'unsigned' => true, 'default' => 0, 'not null' => true ),
'someData' => array( 'type' => 'varchar', 'length' => 24 ),
),
'primary key' => array( 'primaryKey' ),
'indexes' => array( 'in_group' => array( 'group' ) )
);

If the column group is named grp instead, the latter example will work as expected.

Also, if you backquote the index column name yourself...

e.g.
'indexes' => array( 'in_group' => array( '`group`' ) )

Then the schema API will build the table as expected. Inspecting it with the schema module shows the index column name value without the backquotes, but I'm not sure whether that is significant. I have to admit that I was rather surprised that this worked.

I'm not sure if this slightly suspect workaround would cause further problems, so I don't recommend it, but it does clearly demonstrate that the problem is the lack of backquoting of key/unique/index column names when the schema API formulates the CREATE TABLE queries.

There seems to be a scattering of issues in Drupal 6.x (to this point) where awkward column or table names are inconsistently backquoted; handled correctly in various API calls but not in other calls that would appear to be analogous - where you would expect common behaviour.

Unlike the trouble with db_drop_field (which also does not backquote, though db_add_field etcetera do) there is little doubt that this is unintentional.
An issue was raised some time ago: http://drupal.org/node/281058 ... a patch was even produced for _db_create_key_sql(), but was apparently faulty and no further work was done.

Regardless of the drupal policy on preferred table and column names, it's clear that in most cases these values are being correctly quoted and that there has been a partially completed effort to remove restrictions on column and table names, despite assertions that there was no intention to support this. Apparently, Drupal 7 is supposed to resolve this problem properly, so if you are in a position where you are trying to schema table and column names outside your control, you either need to look at 7, or start compiling a more complete patch to the solution and applying it to your 6.x installs manually.

Given this, it's undoubtedly best to avoid using a column name (and probably table name) that requires quotes to work if you possibly can.

Check here an article about to set the index length using the drupal's database schema.
http://nestor.profesional.co.cr/2010/06/05/how-set-index-length-drupal-d...

In spanish:
Lea este articulo sobre como definir el tamaño de un indice utilizando el database schema de drupal.
http://nestor.profesional.co.cr/es/2010/06/04/como-definir-tama-o-un-ind...

~ Nestor

I am trying to setup a foreign key but it is not working. The code runs fine and the table is constructed but there is no foreign key. I even tried to put in garbage data for items such as table name and no error is thrown, it just simply does not create the foreign key. This makes it very hard to tell what I am doing wrong. Here is some psuedo code of what I am trying to do.

  $schema['lookup_table'] = array(
    'description' => 'This is the table that will contain the lookup data.',
    'fields' => array(
      'id' => array(
        'description' => 'Primary key for lookup data.',
        'type' => 'varchar',
        'length' => 4,
        'not null' => TRUE,
        'default' => ''),
      'name' => array(
        'description' => 'Name of lookup item.',
        'type' => 'varchar',
        'length' => 60,
        'not null' => TRUE,
        'default' => ''),
    ),
    'primary key' => array('id'),
  );
  $schema['main_table'] = array(
    'description' => 'This is the table that will reference the lookup table.',
    'fields' => array(
      'id' => array(
        'description' => 'Primary key for main table.',
        'type' => 'varchar',
        'length' => 9,
        'not null' => TRUE,
        'default' => ''),
      'lookup_id' => array(
        'description' => 'ID of lookup item.',
        'type' => 'varchar',
        'length' => 4,
        'not null' => TRUE,
        'default' => ''),
    ),
    'foreign keys' => array(
      'lookup_table_foreign_key' => array(
        'table' => 'lookup_table',
        'columns' => array('lookup_id' => 'id'),
      ),
    ),
    'primary key' => array('id'),
  );

I've tried to reverse the field names in the columns entry as I could not tell from any documentation which is to come first but that did not help either. Placing garbage into the table name such as 'sldkfjowiejfow' did not throw an error and, of course, no foreign key was created.

I am at a loss as to how create this foreign key. Anybody have any ideas??

Also, just FYI, the backend in question is MySQL with InnoDB tables, not MyISAM. So, according to this http://drupal.org/node/111011, it should still parse the code and process it.

The foreign key definitions were added to Drupal 7 for documentation purposes only until a proper method of handling foreign keys is determined (which won't exist until at least D8 now). This data may be helpful for modules such as Schema, but does not have any effect on core.

If you require referential integrity, you will have to code it yourself in the application layer.

If you require an index on the columns in your foreign key, you will have to add an entry to indexes or unique keys; this may result in duplication until foreign keys are actually implemented.

[edit: I have updated the article to reflect this better]

It should be noted here, that datetime field type is not supported by D7 (see http://drupal.org/update/modules/6/7#db_specific_types). Instead, vendor-specific types should be used, i.e.:
'mysql_type' => 'DATETIME',
'pgsql_type' => 'timestamp without time zone',

mysql_suffix is not available in Drupal 7. There doesn't appear to be any replacement way to set the initial auto increment value.

It seems that you need to use 'mysql_character_set' instead, specifically the CREATE TABLE code runs the following:

<?php
    $sql
.= 'ENGINE = ' . $table['mysql_engine'] . ' DEFAULT CHARACTER SET ' . $table['mysql_character_set'];
?>

There's also a 'collation' option:
<?php
   
// By default, MySQL uses the default collation for new tables, which is
    // 'utf8_general_ci' for utf8. If an alternate collation has been set, it
    // needs to be explicitly specified.
    // @see DatabaseConnection_mysql
   
if (!empty($info['collation'])) {
     
$sql .= ' COLLATE ' . $info['collation'];
    }
?>

--
Damien McKenna | Mediacurrent

Should the description text not be wrapped in t()? The original code doesn't include it but I see a few of the commenters have?

I suspect that you'd want it for displaying schema data in Drupal, but it's probably useless for MySql (or Postgres).

Don't t() the description in D7. Or maybe even D6. http://drupal.org/node/224333#schema_translation

I want to add a title key alongside the description. Will this mess up with the API and parsing of the array.

Batandwa Colani
www.starbright.co.za

There's also the 'translatable' key in field definition which is not documented here. You can see it in system.install file. What does it mean?

If you want to set a the primary key to an BLOB/TEXT column, you need to specified a length, otherwise you will got this error:

Syntax error or access violation: 1170 BLOB/TEXT column 'myfield' used in key specification without a key length

Example for Drupal 7.xx:

<?php
...
'primary key' => array(array('myfield',10)),
...
?>