Schema Data Types

The following table shows all the legal combinations of the 'type' and 'size' fields of a column specification along with the underlying database data types used by each combination.

As of Drupal 7, MySQL, PostgreSQL, and SQLite data types are supported in Drupal core.

Drupal 6 core supports MySQL and PostgreSQL.

The maximum size or range of each underlying type is also shown.

For integer types, the number of storage bytes is given;

The maximum range depends on whether the field is signed or unsigned.

For varchar, text, and blob types, the maximum size is given.

[1] MySQL 5.0.3 or later (see http://dev.mysql.com/doc/refman/5.0/en/char.html).
PostgreSQL 9.0 or later (see http://www.postgresql.org/docs/9.0/static/datatype.html).
SQLite 3.0 or later (see http://www.sqlite.org/datatype3.html).

[2] For database portability blob fields must be used to store serialized data since PHP adds null bytes if objects are serialized. Null bytes cannot be stored in Postgres text or varchar fields.

[3] Note that DateTime support was removed from D7 DB API - use mysql_type or pgsql_type if you want this functionality

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 module-name.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' or 'blob'. The types map to the underlying database engine specific datatypes. Use 'serial' for auto incrementing fields. Note also the absence of a datetime type, this should be either stored through other datatypes ('varchar', 'int' and so on) or by using the database driver specific types.
  • '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.
  • 'binary': A boolean indicating that MySQL should force 'char', 'varchar' or 'text' fields to use case-sensitive binary collation. This has no effect on other database types for which case sensitivity is already the default behavior.

  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_engine': In MySQL databases, the engine to use instead of the default. Example:

    'mysql_engine' => 'MyISAM',
  

• 'mysql_character_set': In MySQL databases, the character set to use instead of the default.
• 'collation': In MySQL databases, the collation to use instead of the default.

Important Tips

• Primary Key  - The main key that determines which record it is.
  Generally, this is the auto-inc field, and always changing.
  It's the number used to identify which row you are using.
  Must be unique, which means every row has to have a unique value, which is why they are generally auto incrementing fields.
  Primary keys cannot contain nulls.
  No need to set them as unique id since they already have the requirement of having to be unique.
• Unique ID -  An auto-inc field that changes numbers every time a record is entered to guarantee they are always different.
  Unique keys may contain nulls (but must remain unique)
• Index -  An index is a marker inside the Mysql system which makes it faster to search via that field.
  If you had a field called "name" and it had the names there, and you indexed it then when you search for it those names are in memory and therefore will show faster than a non-indexed field when searched upon.
• Foreign Key -  A foreign key is a reference from a row or rows in one table to a single row in a table (which may be the same table) identified by a primary key or a key that would qualify as a primary key (candidate key).
  Foreign key constraints enforce data integrity, foreign keys (ie indexes) are for relationships between tables.

Example

A key column specifier is either a string naming a column or an array of two elements, column name and length, specifying a prefix of the named column.

As an example, this is the schema definition for the 'users_data' table.
It shows five fields ('uid', 'module', 'name', 'value', and 'serialized'), the primary key (on the 'uid', 'module', and 'name' fields), and two indexes (the 'module' index on the 'module' field and the 'name' index on the 'name' field).

$schema['users_data'] = [
  'description' => 'Stores module data as key/value pairs per user.',
  'fields' => [
    'uid' => [
      'description' => 'The {users}.uid this record affects.',
      'type' => 'int',
      'unsigned' => TRUE,
      'not null' => TRUE,
      'default' => 0,
    ],
    'module' => [
      'description' => 'The name of the module declaring the variable.',
      'type' => 'varchar_ascii',
      'length' => DRUPAL_EXTENSION_NAME_MAX_LENGTH,
      'not null' => TRUE,
      'default' => '',
    ],
    'name' => [
      'description' => 'The identifier of the data.',
      'type' => 'varchar_ascii',
      'length' => 128,
      'not null' => TRUE,
      'default' => '',
    ],
    'value' => [
      'description' => 'The value.',
      'type' => 'blob',
      'not null' => FALSE,
      'size' => 'big',
    ],
    'serialized' => [
      'description' => 'Whether value is serialized.',
      'type' => 'int',
      'size' => 'tiny',
      'unsigned' => TRUE,
      'default' => 0,
    ],
  ],
  'primary key' => [
    'uid',
    'module',
    'name',
  ],
  'indexes' => [
    'module' => [
      'module',
    ],
    'name' => [
      'name',
    ],
  ],
  // For documentation purposes only; foreign keys are not created in the
  // database.
  'foreign keys' => [
    'data_user' => [
      'table' => 'users',
      'columns' => [
        'uid' => 'uid',
      ],
    ],
  ],
];