Drupal7: Add Forgein Key to DB Table Programmatically

drupal-logoIn any complex web applications our data will likely contain relationships. Songs may belong to Albums. A family tree contains siblings, parents, cousins and more distant relatives. All of these things may be represented in a database management system by using foreign keys. A foreign key can is used between tables to reference rows in another table. Typically a DBMS enforces Referential Actions when referenced tables are inserted or updated — this is maintain data integrity in the system. Is it possible for Drupal 7 to make use of foreign keys in schemas?

For historical reasons Drupal 7 and below do not make use of foreign keys in the typical DBMS definition. This is for portability between the major DBMS’s Drupal supports (Drupal supports MySQL, Postgres and SQLite out of the box) and also due to its hook system (see also the Wikipedia article on hooking).

So like any web application tables may be related via foreign keys but Drupal does not rely on or make use of CASCADE or other referential actions provided by many DBMS systems (but not all DBMS systems support FKs, such as SQlite).

But, the Schema API does allow for definitions of foreign key specifications for your tables:

‘foreign keys’: An associative array of relations (‘my_relation’ => specification). Each specification is an array containing the name of the referenced table (‘table’), and an array of column mappings (‘columns’). Column mappings are defined by key pairs (‘source_column’ => ‘referenced_column’).

But, this specification is much like the description key/value in the Schema API. Both of these entries in a schema definition are currently used by the system merely for documentation purposes (poooo! haha).

Knowing that the specification may exist within your schema definitions. If you don’t mind writing non-portable module code (something Drupal Core does not do) then you may create a function similar to the one below in your module.install file to create Foreign Keys for your module using a DBMS of your choice (here I use MySQL):

function _idremotedb_add_foreign_keys() {
  $schema = _idremotedb_schema_definition();
  db_set_active('sa_webextracts');
  foreach ($schema as $table => $table_schema) {
    if (isset($table_schema['foreign keys'])) {
      foreach ($table_schema['foreign keys'] as $fk_name => $fk_details) {
        $src_col = $fk_details['columns'][0];
        $dest_col = $fk_details['columns'][1];
        $dest_table = $fk_details['table'];
        // Create the FK using your DBMS convention. Change this as necessary
        // or add a lookup to the database handler driver type (mysql, postgres, whatever) ...
        db_query("
          ALTER TABLE $table ADD CONSTRAINT $fk_name
          FOREIGN KEY ($src_col) REFERENCES $dest_table($dest_col);
        ");
        drupal_set_message(t('...added foreign key: !fk', array('!fk' => $fk_name)));
      }
    }
  }
  // This index added for either mail-list or login.
  #db_add_index('students', 'uniqueidnumber', 'campusuniqueidnumber');
  db_set_active();
}

The above code would create foreign keys for your table — this way errors would be thrown by the system as Database Error(s) if an invalid relation was made — this might be ugly on your webpage unless you caught the exception in your module and showed the user a helpful message.

Good news — in Drupal 8 it looks like foreign keys may likely be supported in-code for Contributed Modules and via the Database API. Drupal Core will likely not fully make use of foreign keys (yet).

Looking for quality Drupal Hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , , , , , , , , , | Posted under Drupal | RSS 2.0

Author Spotlight

David Gurba

I am a web programmer currently employed at UCSB. I have been developing web applications professionally for 8+ years now. For the last 5 years I’ve been actively developing websites primarily in PHP using Drupal. I have experience using LAMP and developing data driven websites for clients in aviation, higher education and e-commerce. If you’d like to contact me I can be reached at david.gurba@arvixe.com

Leave a Reply

Your email address will not be published. Required fields are marked *