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.
(2011-10-05-foreign-key-example.php) download1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
<span class="line"><span class="cp"><?php</span></span><span class="line"><span class="sd">/**</span></span><span class="line"><span class="sd"> * @file</span></span><span class="line"><span class="sd"> * Installation and upgrade code for Zavod supplier.</span></span><span class="line"><span class="sd"> */</span></span><span class="line"></span><span class="line"><span class="sd">/**</span></span><span class="line"><span class="sd"> * Implements hook_schema().</span></span><span class="line"><span class="sd"> */</span></span><span class="line"><span class="k">function</span> <span class="nf">zavod_supplier_schema</span><span class="p">()</span> <span class="p">{</span></span><span class="line"> <span class="nv">$schema</span> <span class="o">=</span> <span class="k">array</span><span class="p">();</span></span><span class="line"></span><span class="line"> <span class="nv">$schema</span><span class="p">[</span><span class="s1">'zavod_suppliers'</span><span class="p">]</span> <span class="o">=</span> <span class="k">array</span><span class="p">(</span></span><span class="line"> <span class="s1">'description'</span> <span class="o">=></span> <span class="s1">'Stock suppliers for Zavod.'</span><span class="p">,</span></span><span class="line"> <span class="s1">'fields'</span> <span class="o">=></span> <span class="k">array</span><span class="p">(</span></span><span class="line"> <span class="s1">'supplier_id'</span> <span class="o">=></span> <span class="k">array</span><span class="p">(</span></span><span class="line"> <span class="s1">'description'</span> <span class="o">=></span> <span class="s1">'The primary identifier for a supplier.'</span><span class="p">,</span></span><span class="line"> <span class="s1">'type'</span> <span class="o">=></span> <span class="s1">'serial'</span><span class="p">,</span></span><span class="line"> <span class="s1">'unsigned'</span> <span class="o">=></span> <span class="k">TRUE</span><span class="p">,</span></span><span class="line"> <span class="s1">'not null'</span> <span class="o">=></span> <span class="k">TRUE</span><span class="p">,</span></span><span class="line"> <span class="p">),</span></span><span class="line"> <span class="s1">'title'</span> <span class="o">=></span> <span class="k">array</span><span class="p">(</span></span><span class="line"> <span class="s1">'description'</span> <span class="o">=></span> <span class="s1">'The title of this supplier, always treated as non-markup plain text.'</span><span class="p">,</span></span><span class="line"> <span class="s1">'type'</span> <span class="o">=></span> <span class="s1">'text'</span><span class="p">,</span></span><span class="line"> <span class="s1">'not null'</span> <span class="o">=></span> <span class="k">TRUE</span><span class="p">,</span></span><span class="line"> <span class="p">),</span></span><span class="line"> <span class="p">),</span></span><span class="line"> <span class="s1">'primary key'</span> <span class="o">=></span> <span class="k">array</span><span class="p">(</span><span class="s1">'supplier_id'</span><span class="p">),</span></span><span class="line"> <span class="p">);</span></span><span class="line"></span><span class="line"> <span class="nv">$schema</span><span class="p">[</span><span class="s1">'zavod_supply_orders'</span><span class="p">]</span> <span class="o">=</span> <span class="k">array</span><span class="p">(</span></span><span class="line"> <span class="s1">'description'</span> <span class="o">=></span> <span class="s1">'Supply orders for Zavod.'</span><span class="p">,</span></span><span class="line"> <span class="s1">'fields'</span> <span class="o">=></span> <span class="k">array</span><span class="p">(</span></span><span class="line"> <span class="s1">'order_id'</span> <span class="o">=></span> <span class="k">array</span><span class="p">(</span></span><span class="line"> <span class="s1">'description'</span> <span class="o">=></span> <span class="s1">'The primary identifier for a supply order.'</span><span class="p">,</span></span><span class="line"> <span class="s1">'type'</span> <span class="o">=></span> <span class="s1">'serial'</span><span class="p">,</span></span><span class="line"> <span class="s1">'unsigned'</span> <span class="o">=></span> <span class="k">TRUE</span><span class="p">,</span></span><span class="line"> <span class="s1">'not null'</span> <span class="o">=></span> <span class="k">TRUE</span><span class="p">,</span></span><span class="line"> <span class="p">),</span></span><span class="line"> <span class="s1">'supplier_id'</span> <span class="o">=></span> <span class="k">array</span><span class="p">(</span></span><span class="line"> <span class="s1">'description'</span> <span class="o">=></span> <span class="s1">'{zavod_suppliers}.supplier_id of the supplier that the order is made to.'</span><span class="p">,</span></span><span class="line"> <span class="s1">'type'</span> <span class="o">=></span> <span class="s1">'int'</span><span class="p">,</span></span><span class="line"> <span class="s1">'not null'</span> <span class="o">=></span> <span class="k">TRUE</span><span class="p">,</span></span><span class="line"> <span class="s1">'unsigned'</span> <span class="o">=></span> <span class="k">TRUE</span><span class="p">,</span></span><span class="line"> <span class="p">),</span></span><span class="line"> <span class="s1">'title'</span> <span class="o">=></span> <span class="k">array</span><span class="p">(</span></span><span class="line"> <span class="s1">'description'</span> <span class="o">=></span> <span class="s1">'The title of this order, always treated as non-markup plain text.'</span><span class="p">,</span></span><span class="line"> <span class="s1">'type'</span> <span class="o">=></span> <span class="s1">'text'</span><span class="p">,</span></span><span class="line"> <span class="s1">'not null'</span> <span class="o">=></span> <span class="k">TRUE</span><span class="p">,</span></span><span class="line"> <span class="p">),</span></span><span class="line"> <span class="p">),</span></span><span class="line"> <span class="s1">'foreign keys'</span> <span class="o">=></span> <span class="k">array</span><span class="p">(</span></span><span class="line"> <span class="s1">'zavod_suppliers'</span> <span class="o">=></span> <span class="k">array</span><span class="p">(</span></span><span class="line"> <span class="s1">'table'</span> <span class="o">=></span> <span class="s1">'zavod_suppliers'</span><span class="p">,</span></span><span class="line"> <span class="s1">'columns'</span> <span class="o">=></span> <span class="k">array</span><span class="p">(</span><span class="s1">'supplier_id'</span> <span class="o">=></span> <span class="s1">'supplier_id'</span><span class="p">),</span></span><span class="line"> <span class="p">),</span></span><span class="line"> <span class="p">),</span></span><span class="line"> <span class="s1">'primary key'</span> <span class="o">=></span> <span class="k">array</span><span class="p">(</span><span class="s1">'order_id'</span><span class="p">),</span></span><span class="line"> <span class="p">);</span></span><span class="line"></span><span class="line"> <span class="k">return</span> <span class="nv">$schema</span><span class="p">;</span></span><span class="line"><span class="p">}</span></span><span class="line"></span><span class="line"><span class="sd">/**</span></span><span class="line"><span class="sd"> * Implements hook_install().</span></span><span class="line"><span class="sd"> */</span></span><span class="line"><span class="k">function</span> <span class="nf">zavod_supplier_install</span><span class="p">()</span> <span class="p">{</span></span><span class="line"> <span class="c1">// Make real foreign keys.</span></span><span class="line"> <span class="nx">db_query</span><span class="p">(</span><span class="s1">'</span></span><span class="line"><span class="s1"> ALTER TABLE {zavod_supply_orders}</span></span><span class="line"><span class="s1"> ADD CONSTRAINT {zavod_suppliers}</span></span><span class="line"><span class="s1"> FOREIGN KEY (supplier_id) REFERENCES {zavod_suppliers} (supplier_id)</span></span><span class="line"><span class="s1"> '</span><span class="p">);</span></span><span class="line"><span class="p">}</span></span><span class="line"></span><span class="line"><span class="sd">/**</span></span><span class="line"><span class="sd"> * Implements hook_uninstall().</span></span><span class="line"><span class="sd"> */</span></span><span class="line"><span class="k">function</span> <span class="nf">zavod_supplier_uninstall</span><span class="p">()</span> <span class="p">{</span></span><span class="line"> <span class="c1">// Make real foreign keys.</span></span><span class="line"> <span class="nx">db_query</span><span class="p">(</span><span class="s1">'</span></span><span class="line"><span class="s1"> ALTER TABLE {zavod_supply_orders}</span></span><span class="line"><span class="s1"> DROP CONSTRAINT IF EXISTS {zavod_suppliers}</span></span><span class="line"><span class="s1"> '</span><span class="p">);</span></span><span class="line"><span class="p">}</span></span>
It’s pretty self-explanatory if you’re used to Drupal’s schema API. If
you’re not, you should definitely learn.