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

Dev

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

by Javod Khalaj
  • 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

More About the Author

Javod Khalaj

Experience Architect
Cohort Analysis in Tableau: User Retention Given Only Created and Last Seen Dates Cohorts have been the go-to analysis for user retention for a while now, and Tableau has a great article on how to go about creating ...
Use DreamObjects to Backup Your MySQL Database (PHP Script) DreamObjects is DreamHost’s public cloud storage offering. It’s analogous to Amazon’s S3 offering, but slightly cheaper. The nice thing ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072