1
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
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Foreign key indexes</title>
<link rel="stylesheet" href="gettingStarted.css" type="text/css" />
<meta name="generator" content="DocBook XSL Stylesheets V1.73.2" />
<link rel="start" href="index.html" title="Berkeley DB Programmer's Reference Guide" />
<link rel="up" href="am.html" title="Chapter 3. Access Method Operations" />
<link rel="prev" href="am_second.html" title="Secondary indexes" />
<link rel="next" href="am_cursor.html" title="Cursor operations" />
</head>
<body>
<div xmlns="" class="navheader">
<div class="libver">
<p>Library Version 12.1.6.1</p>
</div>
<table width="100%" summary="Navigation header">
<tr>
<th colspan="3" align="center">Foreign key indexes</th>
</tr>
<tr>
<td width="20%" align="left"><a accesskey="p" href="am_second.html">Prev</a> </td>
<th width="60%" align="center">Chapter 3. Access Method Operations </th>
<td width="20%" align="right"> <a accesskey="n" href="am_cursor.html">Next</a></td>
</tr>
</table>
<hr />
</div>
<div class="sect1" lang="en" xml:lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a id="am_foreign"></a>Foreign key indexes</h2>
</div>
</div>
</div>
<p>
Foreign keys are used to ensure a level of consistency
between two different databases in terms of the keys that the
databases use. In a foreign key relationship, one database is
the <span class="emphasis"><em>constrained</em></span> database. This database
is actually a secondary database which is associated with a
primary database. The other database in this relationship is
the <span class="emphasis"><em>foreign key</em></span> database. Once this
relationship has been established between a constrained
database and a foreign key database, then:
</p>
<div class="orderedlist">
<ol type="1">
<li>
<p>
Key/data items cannot be added to the constrained
database unless that same key already exists in the
foreign key database.
</p>
</li>
<li>
<p>
A key/data pair cannot be deleted from the foreign
key database unless some action is also taken to keep
the constrained database consistent with the foreign
key database.
</p>
</li>
</ol>
</div>
<p>
Because the constrained database is a secondary database,
by ensuring it's consistency with a foreign key database you
are actually ensuring that a primary database (the one to
which the secondary database is associated) is consistent with
the foreign key database.
</p>
<p>
Deletions of keys in the foreign key database affect the
constrained database in one of three ways, as specified by the
application:
</p>
<div class="itemizedlist">
<ul type="disc">
<li>
<p>
<code class="literal">Abort</code>
</p>
<p>
The deletion of a record from the foreign database
will not proceed if that key exists in the constrained
primary database. Transactions must be used to prevent
the aborted delete from corrupting either of the
databases.
</p>
</li>
<li>
<p>
<code class="literal">Cascade</code>
</p>
<p>
The deletion of a record from the foreign database
will also cause any records in the constrained primary
database that use that key to also be automatically
deleted.
</p>
</li>
<li>
<p>
<code class="literal">Nullify</code>
</p>
<p>
The deletion of a record from the foreign database
will cause a user specified callback function to be
called, in order to alter or nullify any records using
that key in the constrained primary database.
</p>
</li>
</ul>
</div>
<p>
Note that it is possible to delete a key from the
constrained database, but not from the foreign key database.
For this reason, if you want the keys used in both databases
to be 100% accurate, then you will have to write code to
ensure that when a key is removed from the constrained
database, it is also removed from the foreign key database.
</p>
<p>
As an example of how foreign key indexes might be used,
consider a database of customer information and a database of
order information. A typical customer database would use a
customer ID as the key and those keys would also appear in the
order database. To ensure an order is not booked for a
non-existent customer, the customer database can be associated
with the order database as a foreign index.
</p>
<p>
In order to do this, you create a secondary index of the
order database, which uses customer IDs as the key for its
key/data pairs. This secondary index is, then, the constrained
database. But because the secondary index is constrained, so
too is the order database because the contents of the
secondary index are programmatically tied to the contents of
the order database.
</p>
<p>
The customer database, then, is the foreign key database.
It is associated to the order database's secondary index using
the <a href="../api_reference/C/dbassociate_foreign.html" class="olink">DB->associate_foreign()</a> method. In this way, an order cannot
be added to the order database unless the customer ID already
exists in the customer database.
</p>
<p>
Note that this relationship can also be configured to
delete any outstanding orders for a customer when that
customer is deleted from the customer database.
</p>
<p>
In SQL, this would be done by executing something like the
following:
</p>
<pre class="programlisting">CREATE TABLE customers(cust_id CHAR(4) NOT NULL,
lastname CHAR(15), firstname CHAR(15), PRIMARY KEY(cust_id));
CREATE TABLE orders(order_id CHAR(4) NOT NULL, order_num int NOT NULL,
cust_id CHAR(4), PRIMARY KEY (order_id),
FOREIGN KEY (cust_id) REFERENCES customers(cust_id)
ON DELETE CASCADE); </pre>
<p>
In Berkeley DB, this would work as follows:
</p>
<a id="prog_am18"></a>
<pre class="programlisting">struct customer {
char cust_id[4];
char last_name[15];
char first_name[15];
};
struct order {
char order_id[4];
int order_number;
char cust_id[4];
};
....
void
foreign()
{
DB *dbp, *sdbp, *fdbp;
int ret;
/* Open/create order database */
if ((ret = db_create(&dbp, dbenv, 0)) != 0)
handle_error(ret);
if ((ret = dbp->open(dbp, NULL,
"orders.db", NULL, DB_BTREE, DB_CREATE, 0600)) != 0)
handle_error(ret);
/*
* Open/create secondary index on customer id. Note that it
* supports duplicates because a customer may have multiple
* orders.
*/
if ((ret = db_create(&sdbp, dbenv, 0)) != 0)
handle_error(ret);
if ((ret = sdbp->set_flags(sdbp, DB_DUP | DB_DUPSORT)) != 0)
handle_error(ret);
if ((ret = sdbp->open(sdbp, NULL, "orders_cust_ids.db",
NULL, DB_BTREE, DB_CREATE, 0600)) != 0)
handle_error(ret);
/* Associate the secondary with the primary. */
if ((ret = dbp->associate(dbp, NULL, sdbp, getcustid, 0)) != 0)
handle_error(ret);
/* Open/create customer database */
if ((ret = db_create(&fdbp, dbenv, 0)) != 0)
handle_error(ret);
if ((ret = fdbp->open(fdbp, NULL,
"customers.db", NULL, DB_BTREE, DB_CREATE, 0600)) != 0)
handle_error(ret);
/* Associate the foreign with the secondary. */
if ((ret = fdbp->associate_foreign(
fdbp, sdbp, NULL, DB_FOREIGN_CASCADE)) != 0)
handle_error(ret);
}
/*
* getcustid -- extracts a secondary key (the customer id) from a primary
* key/data pair
*/
int
getcustid(secondary, pkey, pdata, skey)
DB *secondary;
const DBT *pkey, *pdata;
DBT *skey;
{
/*
* Since the secondary key is a simple structure member of the
* record, we don't have to do anything fancy to return it. If
* we have composite keys that need to be constructed from the
* record, rather than simply pointing into it, then the user's
* function might need to allocate space and copy data. In
* this case, the DB_DBT_APPMALLOC flag should be set in the
* secondary key DBT.
*/
memset(skey, 0, sizeof(DBT));
skey->data = ((struct order *)pdata->data)->cust_id;
skey->size = 4;
return (0);
}</pre>
</div>
<div class="navfooter">
<hr />
<table width="100%" summary="Navigation footer">
<tr>
<td width="40%" align="left"><a accesskey="p" href="am_second.html">Prev</a> </td>
<td width="20%" align="center">
<a accesskey="u" href="am.html">Up</a>
</td>
<td width="40%" align="right"> <a accesskey="n" href="am_cursor.html">Next</a></td>
</tr>
<tr>
<td width="40%" align="left" valign="top">Secondary indexes </td>
<td width="20%" align="center">
<a accesskey="h" href="index.html">Home</a>
</td>
<td width="40%" align="right" valign="top"> Cursor operations</td>
</tr>
</table>
</div>
</body>
</html>
|
