Drupal 7 - Usage of Foreign Keys in Schema API and current default FK ERD

12
Apr 2012

Drupal 7 - Usage of Foreign Keys in Schema API and current default FK ERD

comment icon4 comment(s) |
  • 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.

FK Schema

Subscribe to our Networks

Popular Tags by Javod

IW on Facebook

Comments

April 13, 2012

MrPhilbert

Great article

Primary, Unique and especially foreign keys are/were the main reason that complex sites and applications have heretofore used more enterprise databases like Oracle or MSSQL. Now that MySQL defaults to InnoDB, with foreign key capability, Drupal 8 should fully integrate and embrace this. Oh, by the way, sqlite has supported fk's since 2005.

Just imagine adding primary and foreign keys to content types and incorporating those relationships into views! This would totally negate the need for things like the hierarchical select module and reduce its 8,000 lines of code to one where clause in a views query. I find javascript a very poor substitute for enforcing referential integrity. That's what database servers are for.

Need a relationship between entities? Never mind using any of the relation modules. Just add a foreign key.

Don't misinterpret my sentiments. I'm a big fan of Drupal. However, when I need to build a business site or application, it's easier to go to dot net and entity framework.

My prediction: 2 years from now the great cms shootout will not be between Drupal and Joomla or Wordpress. It will be between Drupal and an asp.net mvc (now open source) solution such as Orchard.

April 13, 2012

Coyote

Thanks! Exactly what I was looking for.

I was just about to make a throwaway module just to see what happened when foreign keys are defined in schema. Thanks for saving me the hassle!

April 13, 2012

jkhalaj

Quite true, and I'm excited

Quite true, and I'm excited for that future possibility of defining entity relationships through the database.

April 21, 2012

FGM

Entity Graph module

You may want to use the Entity Graph module to auto-generate such documents : http://drupal.org/sandbox/damz/1438582

Search