• What are Foreign Keys?
• What is the Schema API?
• Why Drupal only supports FK by documentation and not practice.
• Current mappings with an ERD.

What are Foreign Keys?

Foreign keys, put simply, are a referential constraint between two tables on a relational database. What this means is that data from one table is dependent on data from another table, and can not exist without it. This is a constraint placed directly on the database.

For example: let’s say we have two tables, USERS and PRODUCTS. If in our setup we know that under every circumstance a product will be assigned to a user, we can add a foreign key to a column in PRODUCTS that relates back to USERS. If we then try to add a product without having a user assigned to it, the query will fail. This helps force the integrity of data.

When using MySQL I should note that the ISAM engine does not understand foreign keys, however InnoDB does.

What is the Schema API?

The Schema API is an array structure that defines one or more tables.

This is implemented via the hook_schema() function, usually in the myModule.install file. Let’s take a look at a very simple implementation:

function myModule_schema() {
  $schema['myTable'] = array(
    'description' => 'A description of my table.',
    'fields' => array(
      'id' => array(
        'description' => 'The id auto incrementing id of this table (type => serial designates as auto incrementing).',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'items' => array(
        'description' => "The number of whatever in this table.",
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
      'decribe' => array(
        'description' => 'This column, describe, is to store descriptions.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
     ),
    'primary key' => array('id'),
    'foreign keys' => array(
      'fk_name_node' => array(
        'table' => 'node',
        'columns' => array('id' => 'nid'),
      ),
     ),
  );
}

Right now let’s concentrate on the way foreign keys are setup through the Scema API.

We first create an array called foreign keys.

'foreign keys' => array(

We now give our foreign key an identifying name.

'fk_name_node' => array(

Next we designate which table will be our reference, in this case the ‘node’ table.

'table' => 'node',

Finally we assign the foreign key column relations from our table to the constraining table.

'columns' => array('id' => 'nid'), ),

Why Drupal only supports FK by documentation and not practice.

So now that we know this what can we do? Well, currently Drupal only supports Foreign Keys as a documentation feature. Even when you’ve defined it in your schema, it will not actually implement the constraints on the database. So what’s the point? Well for one, it helps those writing contributing modules to quickly see what tables are a required reference, but secondly, it is slowly preparing Drupal to actually add Foreign Keys. We might see this in Drupal 8, but probably not until a later release. Still, it’s a good idea to implement this feature in your schemas, and those working on core are doing it in theirs.

Current mappings with an ERD.

I took a look over the default install of Drupal 7, and grabbed all the schemas with foreign keys. Then I created an Entity Relationship Diagram (ERD) with ONLY those tables that had foreign key constraints. Click the image below for the full size pdf. This isn’t every table that exists on default install, just those that had a foreign key constraint written into its schema.