Using Database-level Foreign Keys in Drupal 7
If you use a good database system, foreign keys is an actual concept on the server that is used to enforce data integrity.
With database-level foreign keys, it becomes impossible to break your data by deleting data referenced by other data, without also dealing with the referenced data.
In practice this means that deleting a node from the database could be
as simple as DELETE FROM node WHERE nid = 53 CASCADE, and conversely
it would not be allowed to delete the row from the node table, as long
as it is referenced in node_revision, field_something_something and
the umpteen other tables a Drupal site is likely to have that depends on
the node table.
Currently, it’s neigh-impossible to delete a node or another entity from the database without using Drupal’s API. This is a sticky problem if you ever need to share the database between systems.
In Drupal 7, the syntax to define foreign keys were introduced to Drupal’s schema API. The schema API is used to explain the database structure to Drupal, so it can be understood and utilised by modules like Views. This understanding is also translated into SQL code, when tables are created by Drupal’s install scripts.
However, the foreign key syntax introduced in Drupal 7 does not affect the database structure at all, it is only used inside Drupal for relating one table to another. Perhaps in time, Drupal will also create the foreign keys at the database level, but until that happens, you will need to create them manually. Here’s how to do it.
An example
In the following example, I define two tables, and then use hook_install and hook_uninstall to set up and dismantle the foreign keys.
<?php
/**
* @file
* Installation and upgrade code for Zavod supplier.
*/
/**
* Implements hook_schema().
*/
function zavod_supplier_schema() {
$schema = array();
$schema['zavod_suppliers'] = array(
'description' => 'Stock suppliers for Zavod.',
'fields' => array(
'supplier_id' => array(
'description' => 'The primary identifier for a supplier.',
'type' => 'serial',
'unsigned' => TRUE,
'not null' => TRUE,
),
'title' => array(
'description' => 'The title of this supplier, always treated as non-markup plain text.',
'type' => 'text',
'not null' => TRUE,
),
),
'primary key' => array('supplier_id'),
);
$schema['zavod_supply_orders'] = array(
'description' => 'Supply orders for Zavod.',
'fields' => array(
'order_id' => array(
'description' => 'The primary identifier for a supply order.',
'type' => 'serial',
'unsigned' => TRUE,
'not null' => TRUE,
),
'supplier_id' => array(
'description' => '{zavod_suppliers}.supplier_id of the supplier that the order is made to.',
'type' => 'int',
'not null' => TRUE,
'unsigned' => TRUE,
),
'title' => array(
'description' => 'The title of this order, always treated as non-markup plain text.',
'type' => 'text',
'not null' => TRUE,
),
),
'foreign keys' => array(
'zavod_suppliers' => array(
'table' => 'zavod_suppliers',
'columns' => array('supplier_id' => 'supplier_id'),
),
),
'primary key' => array('order_id'),
);
return $schema;
}
/**
* Implements hook_install().
*/
function zavod_supplier_install() {
// Make real foreign keys.
db_query('
ALTER TABLE {zavod_supply_orders}
ADD CONSTRAINT {zavod_suppliers}
FOREIGN KEY (supplier_id) REFERENCES {zavod_suppliers} (supplier_id)
');
}
/**
* Implements hook_uninstall().
*/
function zavod_supplier_uninstall() {
// Make real foreign keys.
db_query('
ALTER TABLE {zavod_supply_orders}
DROP CONSTRAINT IF EXISTS {zavod_suppliers}
');
}
It’s pretty self-explanatory if you’re used to Drupal’s schema API. If you’re not, you should definitely learn.