Debian Codegraph @

www/foreignkeys.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>SQLite Foreign Key Support</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 SQLite Foreign Key Support
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#fk_basics">1. Introduction to Foreign Key Constraints</a></div>
126 <div class="fancy-toc1"><a href="#fk_enable">2.
127 Enabling Foreign Key Support
128 </a></div>
129 <div class="fancy-toc1"><a href="#fk_indexes">3. Required and Suggested Database Indexes</a></div>
130 <div class="fancy-toc1"><a href="#fk_advanced">4. Advanced Foreign Key Constraint Features</a></div>
131 <div class="fancy-toc2"><a href="#fk_composite">4.1. Composite Foreign Key Constraints</a></div>
132 <div class="fancy-toc2"><a href="#fk_deferred">4.2. Deferred Foreign Key Constraints</a></div>
133 <div class="fancy-toc2"><a href="#fk_actions">4.3.
134 ON DELETE and ON UPDATE Actions
135 </a></div>
136 <div class="fancy-toc1"><a href="#fk_schemacommands">5. CREATE, ALTER and DROP TABLE commands</a></div>
137 <div class="fancy-toc1"><a href="#fk_unsupported">6. Limits and Unsupported Features</a></div>
138 </div>
139 </details>
140 </div>
141
142
143
144
145
146 <h2 style="margin-left:1.0em" notoc="1" id="overview"> Overview</h2>
147
148 <p>This document describes the support for SQL foreign key constraints
149 introduced in SQLite <a href="releaselog/3_6_19.html">version 3.6.19</a> (2009-10-14).
150
151 </p><p>The first section introduces the
152 concept of an SQL foreign key by example and defines the terminology
153 used for the remainder of the document. Section 2 describes the steps
154 an application must take in order to enable foreign key constraints in
155 SQLite (it is disabled by default). The next section, section 3,
156 describes the indexes that the user must create in order to use
157 foreign key constraints, and those that should be created in order for
158 foreign key constraints to function efficiently. Section 4 describes
159 the advanced foreign key related features supported by SQLite and
160 section 5 describes the way the <a href="lang_altertable.html">ALTER</a> and <a href="lang_droptable.html">DROP TABLE</a> commands are
161 enhanced to support foreign key constraints. Finally, section 6
162 enumerates the missing features and limits of the current implementation.
163
164 </p><p>This document does not contain a full description of the syntax used
165 to create foreign key constraints in SQLite. This may be found as
166 part of the documentation for the <a href="lang_createtable.html">CREATE TABLE</a> statement.
167
168 </p><h1 id="fk_basics"><span>1. </span>Introduction to Foreign Key Constraints</h1>
169
170 <p>
171 SQL foreign key constraints are used to enforce "exists" relationships
172 between tables. For example, consider a database schema created using
173 the following SQL commands:
174
175 </p><div class="codeblock"><pre>CREATE TABLE artist(
176 artistid INTEGER PRIMARY KEY,
177 artistname TEXT
178 );
179 CREATE TABLE track(
180 trackid INTEGER,
181 trackname TEXT,
182 trackartist INTEGER <b>-- Must map to an artist.artistid!</b>
183 );
184 </pre></div>
185
186 <p>
187 The applications using this database are entitled to assume that for
188 each row in the <i>track</i> table there exists a corresponding row in the
189 <i>artist</i> table. After all, the comment in the declaration says so.
190 Unfortunately, if a user edits the database using an external tool or
191 if there is a bug in an application, rows might be inserted into the
192 <i>track</i> table that do not correspond to any row in the <i>artist</i>
193 table. Or rows might be deleted from the <i>artist</i> table, leaving
194 orphaned rows in the <i>track</i> table that do not correspond to any of
195 the remaining rows in <i>artist</i>. This might cause the application
196 or applications to malfunction later on, or at least make coding the
197 application more difficult.
198
199 </p><p>
200 One solution is to add an SQL foreign key constraint to the database
201 schema to enforce the relationship between the <i>artist</i> and
202 <i>track</i> table. To do so, a foreign key definition may be added
203 by modifying the declaration of the <i>track</i> table to the following:
204
205 </p><div class="codeblock"><pre>CREATE TABLE track(
206 trackid INTEGER,
207 trackname TEXT,
208 trackartist INTEGER,
209 <b>FOREIGN KEY(trackartist) REFERENCES artist(artistid)</b>
210 );
211 </pre></div>
212
213 <p>
214 This way, the constraint is enforced by SQLite. Attempting to insert
215 a row into the <i>track</i> table that does not correspond to any
216 row in the <i>artist</i> table will fail, as will attempting to
217 delete a row from the <i>artist</i> table when there exist dependent
218 rows in the <i>track</i> table. There is one exception: if the foreign
219 key column for an entry in the <i>track</i> table is NULL, then no corresponding
220 entry in the <i>artist</i> table is required. Expressed in SQL, this
221 means that for every row in the <i>track</i> table, the following
222 expression evaluates to true:
223 </p><div class="codeblock"><pre>trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
224 </pre></div>
225
226 <p>Tip: If the application requires a stricter relationship between
227 <i>artist</i> and <i>track</i>, where NULL values are not permitted
228 in the <i>trackartist</i> column, simply add the appropriate
229 "NOT NULL" constraint to the schema.
230
231 </p><p>There are several other ways to add an equivalent foreign key declaration
232 to a <a href="lang_createtable.html">CREATE TABLE</a> statement. Refer to the
233 <a href="lang_createtable.html">CREATE TABLE documentation</a> for details.
234
235 </p><p>The following SQLite command-line session illustrates the effect of the
236 foreign key constraint added to the <i>track</i> table:
237
238 </p><div class="codeblock"><pre>sqlite&gt; SELECT * FROM artist;
239 artistid artistname
240 -------- -----------------
241 1 Dean Martin
242 2 Frank Sinatra
243
244 sqlite> SELECT * FROM track;
245 trackid trackname trackartist
246 ------- ----------------- -----------
247 11 That's Amore 1
248 12 Christmas Blues 1
249 13 My Way 2
250
251 sqlite&gt; <i>-- This fails because the value inserted into the trackartist column (3)</i>
252 sqlite&gt; <i>-- does not correspond to any row in the artist table.</i>
253 sqlite&gt; INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
254 <b>SQL error: foreign key constraint failed</b>
255
256 sqlite&gt; <i>-- This succeeds because a NULL is inserted into trackartist. A</i>
257 sqlite&gt; <i>-- corresponding row in the artist table is not required in this case.</i>
258 sqlite&gt; INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL);
259
260 sqlite&gt; <i>-- Trying to modify the trackartist field of the record after it has </i>
261 sqlite&gt; <i>-- been inserted does not work either, since the new value of trackartist (3)</i>
262 sqlite&gt; <i>-- Still does not correspond to any row in the artist table.</i>
263 sqlite&gt; UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
264 <b>SQL error: foreign key constraint failed</b>
265
266 sqlite&gt; <i>-- Insert the required row into the artist table. It is then possible to</i>
267 sqlite&gt; <i>-- update the inserted row to set trackartist to 3 (since a corresponding</i>
268 sqlite&gt; <i>-- row in the artist table now exists).</i>
269 sqlite&gt; INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
270 sqlite&gt; UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
271
272 sqlite&gt; <i>-- Now that "Sammy Davis Jr." (artistid = 3) has been added to the database,</i>
273 sqlite&gt; <i>-- it is possible to INSERT new tracks using this artist without violating</i>
274 sqlite&gt; <i>-- the foreign key constraint:</i>
275 sqlite&gt; INSERT INTO track VALUES(15, 'Boogie Woogie', 3);
276 </pre></div>
277
278 <p>
279 As you would expect, it is not possible to manipulate the database to a state
280 that violates the foreign key constraint by deleting or updating rows in the
281 <i>artist</i> table either:
282 </p><div class="codeblock"><pre>sqlite&gt; <i>-- Attempting to delete the artist record for "Frank Sinatra" fails, since</i>
283 sqlite&gt; <i>-- the track table contains a row that refers to it.</i>
284 sqlite&gt; DELETE FROM artist WHERE artistname = 'Frank Sinatra';
285 <b>SQL error: foreign key constraint failed</b>
286
287 sqlite&gt; <i>-- Delete all the records from the track table that refer to the artist</i>
288 sqlite&gt; <i>-- "Frank Sinatra". Only then is it possible to delete the artist.</i>
289 sqlite&gt; DELETE FROM track WHERE trackname = 'My Way';
290 sqlite&gt; DELETE FROM artist WHERE artistname = 'Frank Sinatra';
291
292 sqlite&gt; <i>-- Try to update the artistid of a row in the artist table while there</i>
293 sqlite&gt; <i>-- exists records in the track table that refer to it. </i>
294 sqlite&gt; UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
295 <b>SQL error: foreign key constraint failed</b>
296
297 sqlite&gt; <i>-- Once all the records that refer to a row in the artist table have</i>
298 sqlite&gt; <i>-- been deleted, it is possible to modify the artistid of the row.</i>
299 sqlite&gt; DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');
300 sqlite&gt; UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
301 </pre></div>
302
303 <p>
304 SQLite uses the following terminology:
305 <a name="parentchild"></a>
306
307 </p><ul>
308 <li><p>The <b>parent table</b> is the table that the foreign key constraint
309 refers to. The parent table in the example in this section is the
310 <i>artist</i> table. Some books and articles refer to this as the
311 <i>referenced table</i>, which is arguably more correct, but tends
312 to lead to confusion.
313
314 </p></li><li><p>The <b>child table</b> is the table that the foreign key constraint
315 is applied to and the table that contains the REFERENCES clause.
316 The example in this section uses the <i>track</i> table
317 as the child table. Other books and articles refer to this as the
318 <i>referencing table</i>.
319
320 </p></li><li><p>The <b>parent key</b> is the column or set of columns in the parent
321 table that the foreign key constraint refers to. This is normally, but
322 not always, the primary key of the parent table. The parent key must
323 be a named column or columns in the parent table, not the <a href="lang_createtable.html#rowid">rowid</a>.
324
325 </p></li><li><p>The <b>child key</b> is the column or set of columns in the child
326 table that are constrained by the foreign key constraint and which
327 hold the REFERENCES clause.
328 </p></li></ul>
329
330 <p>
331 The foreign key constraint is satisfied if for each row in the child table
332 either one or more of the child key columns are NULL, or there exists a
333 row in the parent table for which each parent key column contains a value
334 equal to the value in its associated child key column.
335
336 </p><p>
337 In the above paragraph, the term "equal" means equal when values are
338 compared using the rules <a href="datatype3.html#comparisons">specified
339 here</a>. The following clarifications apply:
340
341 </p><ul>
342 <li><p>When comparing text values, the <a href="datatype3.html#collation">collating sequence</a>
343 associated with the parent key column is always used.
344 </p></li><li><p>When comparing values, if the parent key column has an <a href="datatype3.html#affinity">affinity</a>,
345 then that affinity is applied to the child key value before the
346 comparison is performed.
347 </p></li></ul>
348
349 <a name="fk_enable"></a>
350 <h1 id="fk_enable" tags="foreign key constraints are enabled"><span>2. </span>
351 Enabling Foreign Key Support
352 </h1>
353 <p>
354 In order to use foreign key constraints in SQLite, the library must
355 be compiled with neither <a href="compile.html#omit_foreign_key">SQLITE_OMIT_FOREIGN_KEY</a> nor
356 <a href="compile.html#omit_trigger">SQLITE_OMIT_TRIGGER</a> defined. If SQLITE_OMIT_TRIGGER is defined
357 but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior
358 to <a href="releaselog/3_6_19.html">version 3.6.19</a> (2009-10-14)
359 - foreign key definitions are parsed and may be
360 queried using <a href="pragma.html#pragma_foreign_key_list">PRAGMA foreign_key_list</a>, but foreign key constraints
361 are not enforced. The <a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys</a> command is a no-op in this
362 configuration. If OMIT_FOREIGN_KEY is defined, then foreign key
363 definitions cannot even be parsed (attempting to specify a foreign
364 key definition is a syntax error).
365
366 </p><p>
367 Assuming the library is compiled with foreign key constraints enabled,
368 it must still be enabled by the application at runtime, using the
369 <a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys</a> command. For example:
370
371 </p><div class="codeblock"><pre>sqlite&gt; PRAGMA foreign_keys = ON;
372 </pre></div>
373
374 <p>
375 Foreign key constraints are disabled by default
376 (for backwards compatibility),
377 so must be enabled separately for each <a href="c3ref/sqlite3.html">database connection</a>.
378 (Note, however, that future releases of SQLite might change
379 so that foreign key constraints enabled by default. Careful
380 developers will not
381 make any assumptions about whether or not foreign keys are enabled by
382 default but will instead enable or disable them as necessary.)
383 The application can also use a <a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys</a> statement to
384 determine if foreign keys are currently enabled. The following
385 command-line session demonstrates this:
386 </p><div class="codeblock"><pre>sqlite> PRAGMA foreign_keys;
387 0
388 sqlite> PRAGMA foreign_keys = ON;
389 sqlite> PRAGMA foreign_keys;
390 1
391 sqlite> PRAGMA foreign_keys = OFF;
392 sqlite> PRAGMA foreign_keys;
393 0
394 </pre></div>
395
396 <p>Tip: If the command "PRAGMA foreign_keys" returns no data instead of a
397 single row containing "0" or "1", then the version of SQLite you are
398 using does not support foreign keys (either because it is older than
399 3.6.19 or because it was compiled with <a href="compile.html#omit_foreign_key">SQLITE_OMIT_FOREIGN_KEY</a> or
400 <a href="compile.html#omit_trigger">SQLITE_OMIT_TRIGGER</a> defined).
401
402 </p><p>
403 It is not possible to enable or disable foreign key constraints
404 in the middle of a <a href="lang_transaction.html">multi-statement transaction</a> (when SQLite
405 is not in <a href="c3ref/get_autocommit.html">autocommit mode</a>). Attempting to do so does not return
406 an error; it simply has no effect.
407
408 </p><h1 id="fk_indexes"><span>3. </span>Required and Suggested Database Indexes</h1>
409
410 <p>
411 Usually, the parent key of a foreign key constraint is the primary key of
412 the parent table. If they are not the primary key, then the parent key
413 columns must be collectively subject to a UNIQUE constraint or have
414 a UNIQUE index.
415 If the parent key columns have a UNIQUE index,
416 then that index must use the collation sequences that are specified
417 in the CREATE TABLE statement for the parent table.
418 For example,
419
420 </p><div class="codeblock"><pre>CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);
421 CREATE UNIQUE INDEX i1 ON parent(c, d);
422 CREATE INDEX i2 ON parent(e);
423 CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase);
424
425 CREATE TABLE child1(f, g REFERENCES parent(a)); <i>-- Ok</i>
426 CREATE TABLE child2(h, i REFERENCES parent(b)); <i>-- Ok</i>
427 CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); <i>-- Ok</i>
428 CREATE TABLE child4(l, m REFERENCES parent(e)); <i>-- Error!</i>
429 CREATE TABLE child5(n, o REFERENCES parent(f)); <i>-- Error!</i>
430 CREATE TABLE child6(p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c)); <i>-- Error!</i>
431 CREATE TABLE child7(r REFERENCES parent(c)); <i>-- Error!</i>
432 </pre></div>
433
434 <p>
435 The foreign key constraints created as part of tables <i>child1</i>,
436 <i>child2</i> and <i>child3</i> are all fine. The foreign key
437 declared as part of table <i>child4</i> is an error because even though
438 the parent key column is indexed, the index is not UNIQUE.
439 The foreign key for table <i>child5</i>
440 is an error because even though the parent key column has a unique
441 index, the index uses a different collating sequence.
442 Tables <i>child6</i> and <i>child7</i> are incorrect because while
443 both have UNIQUE indices on their parent keys, the keys are not an
444 exact match to the columns of a single UNIQUE index.
445
446 </p><p>
447 If the database schema contains foreign key errors that require looking
448 at more than one table definition to identify, then those errors are not
449 detected when the tables are created. Instead, such errors prevent
450 the application from preparing SQL statements that modify the content
451 of the child or parent tables in ways that use the foreign keys.
452 Errors reported when content is changed are "DML errors" and errors
453 reported when the schema is changed are "DDL errors".
454 So, in other words, misconfigured foreign key constraints that require
455 looking at both the child and parent are DML errors.
456 The English language error message for foreign key DML errors is usually
457 "foreign key mismatch" but can also be "no such table" if the parent
458 table does not exist.
459 Foreign key DML errors are reported if:
460
461 </p><ul>
462 <li> The parent table does not exist, or
463 </li><li> The parent key columns named in the foreign key constraint do
464 not exist, or
465 </li><li> The parent key columns named in the foreign key constraint are not
466 the primary key of the parent table and are not subject to a unique
467 constraint using collating sequence specified in the CREATE TABLE, or
468 </li><li> The child table references the primary key of the parent without
469 specifying the primary key columns and the number of primary key
470 columns in the parent do not match the number of child key columns.
471 </li></ul>
472
473 <p>
474 The last bullet above is illustrated by the following:
475
476 </p><div class="codeblock"><pre>CREATE TABLE parent2(a, b, PRIMARY KEY(a,b));
477
478 CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2); <i>-- Ok</i>
479 CREATE TABLE child9(x REFERENCES parent2); <i>-- Error!</i>
480 CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2); <i>-- Error!</i>
481 </pre></div>
482
483 <p>
484 By contrast, if foreign key errors can be recognized simply by looking
485 at the definition of the child table and without having to consult the
486 parent table definition, then the
487 <a href="lang_createtable.html">CREATE TABLE</a> statement for the child table fails. Because the error
488 occurs during a schema change, this is a DDL error.
489 Foreign key DDL errors are reported regardless of
490 whether or not foreign key constraints are enabled when the
491 table is created.
492
493 </p><p>
494 Indices are not required for child key columns but they are almost
495 always beneficial. Returning to
496 the example in <a href="#fk_basics">section 1</a>, each time an application
497 deletes a row from the <i>artist</i> table (the parent table), it
498 performs the equivalent of the following SELECT statement to search
499 for referencing rows in the <i>track</i> table (the child table).
500
501 </p><div class="codeblock"><pre>SELECT rowid FROM track WHERE trackartist = ?
502 </pre></div>
503
504 <p>
505 where ? in the above is replaced with the value of the <i>artistid</i>
506 column of the record being deleted from the <i>artist</i> table (recall
507 that the <i>trackartist</i> column is the child key and the <i>artistid</i>
508 column is the parent key). Or, more generally:
509
510 </p><div class="codeblock"><pre>SELECT rowid FROM &lt;child-table&gt; WHERE &lt;child-key&gt; = :parent_key_value
511 </pre></div>
512
513 <p>
514 If this SELECT returns any rows at all, then SQLite concludes that
515 deleting the row from the parent table would violate the foreign key
516 constraint and returns an error.
517 Similar queries may be run if the content of the parent key
518 is modified or a new row is inserted into the parent table.
519 If these queries cannot use an index, they are forced to do a
520 linear scan of the entire child table. In a non-trivial database, this may
521 be prohibitively expensive.
522
523 </p><p>
524 So, in most real systems, an index should be created on the child key columns
525 of each foreign key constraint. The child key index does not have
526 to be (and usually will not be) a UNIQUE index.
527 Returning again to the example in section 1, the
528 complete database schema for efficient implementation of the foreign key
529 constraint might be:
530
531 </p><div class="codeblock"><pre>CREATE TABLE artist(
532 artistid INTEGER PRIMARY KEY,
533 artistname TEXT
534 );
535 CREATE TABLE track(
536 trackid INTEGER,
537 trackname TEXT,
538 trackartist INTEGER REFERENCES artist
539 );
540 CREATE INDEX trackindex ON track(trackartist);
541 </pre></div>
542
543 <p>
544 The block above uses a shorthand form to create the foreign key constraint.
545 Attaching a "REFERENCES <i>&lt;parent-table&gt;</i>" clause to a column
546 definition creates a foreign key constraint that maps the column to the
547 primary key of <i>&lt;parent-table&gt;</i>. Refer to the <a href="lang_createtable.html">CREATE TABLE</a>
548 documentation for further details.
549
550
551 </p><h1 id="fk_advanced"><span>4. </span>Advanced Foreign Key Constraint Features</h1>
552
553 <h2 id="fk_composite"><span>4.1. </span>Composite Foreign Key Constraints</h2>
554
555 <p>
556 A composite foreign key constraint is one where the child and parent keys
557 are both composite keys. For example, consider
558 the following database schema:
559
560 </p><div class="codeblock"><pre>CREATE TABLE album(
561 albumartist TEXT,
562 albumname TEXT,
563 albumcover BINARY,
564 PRIMARY KEY(albumartist, albumname)
565 );
566
567 CREATE TABLE song(
568 songid INTEGER,
569 songartist TEXT,
570 songalbum TEXT,
571 songname TEXT,
572 <b>FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)</b>
573 );
574 </pre></div>
575
576 <p>
577 In this system, each entry in the song table is required to map to an entry
578 in the album table with the same combination of artist and album.
579
580 </p><p>
581 Parent and child keys must have the same cardinality.
582 In SQLite, if any of the child key columns (in this case songartist
583 and songalbum) are NULL, then there is no requirement for a corresponding
584 row in the parent table.
585
586 </p><h2 id="fk_deferred"><span>4.2. </span>Deferred Foreign Key Constraints</h2>
587
588 <p>
589 Each foreign key constraint in SQLite is classified as either immediate
590 or deferred. Foreign key constraints are immediate by default.
591 All the foreign key examples presented
592 so far have been of immediate foreign key constraints.
593
594 </p><p>
595 If a statement modifies the contents of the database so that an immediate
596 foreign key constraint is in violation at the conclusion the statement,
597 an exception is thrown and
598 the effects of the statement are reverted. By contrast, if
599 a statement modifies the contents of the database such that a deferred
600 foreign key constraint is violated, the violation is not reported
601 immediately. Deferred foreign key constraints are not checked
602 until the transaction tries to <a href="lang_transaction.html">COMMIT</a>.
603 For as long as the user has
604 an open transaction, the database is allowed to exist in a state that
605 violates any number of deferred foreign key constraints. However,
606 <a href="lang_transaction.html">COMMIT</a> will fail as long as foreign key constraints remain in
607 violation.
608
609 </p><p>
610 If the current statement is not inside an explicit transaction (a
611 <a href="lang_transaction.html">BEGIN</a>/<a href="lang_transaction.html">COMMIT</a>/<a href="lang_transaction.html">ROLLBACK</a> block), then an implicit
612 transaction is committed
613 as soon as the statement has finished executing. In this case deferred
614 constraints behave the same as immediate constraints.
615
616 </p><p>
617 To mark a foreign key constraint as deferred, its declaration must
618 include the following clause:
619
620 </p><div class="codeblock"><pre>DEFERRABLE INITIALLY DEFERRED <i>-- A deferred foreign key constraint</i>
621 </pre></div>
622
623 <p>
624 The full syntax for specifying foreign key constraints is available as part
625 of the <a href="lang_createtable.html">CREATE TABLE</a> documentation. Replacing the phrase above
626 with any of the following
627 creates an immediate foreign key constraint.
628
629 </p><div class="codeblock"><pre>NOT DEFERRABLE INITIALLY DEFERRED <i>-- An immediate foreign key constraint</i>
630 NOT DEFERRABLE INITIALLY IMMEDIATE <i>-- An immediate foreign key constraint</i>
631 NOT DEFERRABLE <i>-- An immediate foreign key constraint</i>
632 DEFERRABLE INITIALLY IMMEDIATE <i>-- An immediate foreign key constraint</i>
633 DEFERRABLE <i>-- An immediate foreign key constraint</i>
634 </pre></div>
635
636 <p>The <a href="pragma.html#pragma_defer_foreign_keys">defer_foreign_keys pragma</a> can be used to temporarily change all foreign
637 key constraints to deferred regardless of how they are declared.
638
639 </p><p>
640 The following example illustrates the effect of using a deferred foreign
641 key constraint.
642
643 </p><div class="codeblock"><pre><i>-- Database schema. Both tables are initially empty. </i>
644 CREATE TABLE artist(
645 artistid INTEGER PRIMARY KEY,
646 artistname TEXT
647 );
648 CREATE TABLE track(
649 trackid INTEGER,
650 trackname TEXT,
651 trackartist INTEGER REFERENCES artist(artistid) <b>DEFERRABLE INITIALLY DEFERRED</b>
652 );
653
654 sqlite3&gt; <i>-- If the foreign key constraint were immediate, this INSERT would</i>
655 sqlite3&gt; <i>-- cause an error (since as there is no row in table artist with</i>
656 sqlite3&gt; <i>-- artistid=5). But as the constraint is deferred and there is an</i>
657 sqlite3&gt; <i>-- open transaction, no error occurs.</i>
658 sqlite3&gt; BEGIN;
659 sqlite3&gt; INSERT INTO track VALUES(1, 'White Christmas', 5);
660
661 sqlite3&gt; <i>-- The following COMMIT fails, as the database is in a state that</i>
662 sqlite3&gt; <i>-- does not satisfy the deferred foreign key constraint. The</i>
663 sqlite3&gt; <i>-- transaction remains open.</i>
664 sqlite3&gt; COMMIT;
665 <b>SQL error: foreign key constraint failed</b>
666
667 sqlite3&gt; <i>-- After inserting a row into the artist table with artistid=5, the</i>
668 sqlite3&gt; <i>-- deferred foreign key constraint is satisfied. It is then possible</i>
669 sqlite3&gt; <i>-- to commit the transaction without error.</i>
670 sqlite3&gt; INSERT INTO artist VALUES(5, 'Bing Crosby');
671 sqlite3&gt; COMMIT;
672 </pre></div>
673 <p>
674 A <a href="lang_savepoint.html">nested savepoint</a> transaction may be RELEASEd while the
675 database is in a state that does not satisfy a deferred foreign key
676 constraint. A transaction savepoint (a non-nested savepoint that was
677 opened while there was not currently an open transaction), on the
678 other hand, is subject to the same restrictions as a COMMIT - attempting
679 to RELEASE it while the database is in such a state will fail.
680
681 </p><p>
682 If a COMMIT statement (or the RELEASE of a transaction SAVEPOINT) fails
683 because the database is currently in a state that violates a deferred
684 foreign key constraint and there are currently
685 <a href="lang_savepoint.html">nested savepoints</a>, the nested savepoints remain open.
686
687
688 </p><a name="fk_actions"></a>
689 <h2 id="fk_actions" tags="foreign key actions"><span>4.3. </span>
690 ON DELETE and ON UPDATE Actions
691 </h2>
692
693 <p>
694 Foreign key ON DELETE and ON UPDATE clauses are used to configure actions
695 that take place when deleting rows from the parent table (ON DELETE), or
696 modifying the parent key values of existing rows (ON UPDATE). A single
697 foreign key constraint may have different actions configured for ON DELETE
698 and ON UPDATE. Foreign key actions are similar to triggers in many ways.
699
700 </p><p>
701 The ON DELETE and ON UPDATE action associated with each foreign key in an
702 SQLite database is one of "NO ACTION", "RESTRICT", "SET NULL",
703 "SET DEFAULT" or "CASCADE". If an action is not explicitly specified, it
704 defaults to "NO ACTION".
705
706 </p><ul>
707 <li><p> <b>NO ACTION</b>: Configuring "NO ACTION" means just that: when a
708 parent key is modified or deleted from the database, no special action is
709 taken.
710
711 </p></li><li><p> <b>RESTRICT</b>: The "RESTRICT" action means that the application
712 is prohibited from deleting (for ON DELETE RESTRICT) or modifying
713 (for ON UPDATE RESTRICT) a parent key when there exists one or more child
714 keys mapped to it. The difference between the effect of a RESTRICT
715 action and normal foreign key constraint enforcement is that the
716 RESTRICT action processing happens as soon as the field is updated -
717 not at the end of the current statement as it would with an immediate
718 constraint, or at the end of the current transaction as it would with
719 a deferred constraint.
720 Even if the foreign key constraint it is
721 attached to is deferred, configuring a RESTRICT action causes SQLite to
722 return an error immediately if a parent key with dependent child keys is
723 deleted or modified.
724
725 </p></li><li><p> <b>SET NULL</b>: If the configured action is "SET NULL", then when
726 a parent key is deleted (for ON DELETE SET NULL) or modified (for ON
727 UPDATE SET NULL), the child key columns of all rows in the child table
728 that mapped to the parent key are set to contain SQL NULL values.
729
730 </p></li><li><p> <b>SET DEFAULT</b>: The "SET DEFAULT" actions are similar to
731 "SET NULL",
732 except that each of the child key columns is set to contain the column's
733 default value instead of NULL. Refer to the <a href="lang_createtable.html">CREATE TABLE</a>
734 documentation for details on how default values are assigned to table
735 columns.
736
737 </p></li><li><p> <b>CASCADE</b>: A "CASCADE" action propagates the delete or update
738 operation on the parent key to each dependent child key. For an "ON
739 DELETE CASCADE" action, this means that each row in the child table that
740 was associated with the deleted parent row is also deleted. For an "ON
741 UPDATE CASCADE" action, it means that the values stored in each dependent
742 child key are modified to match the new parent key values.
743 </p></li></ul>
744
745 <p>
746 For example, adding an "ON UPDATE CASCADE" clause to the foreign key as
747 shown below enhances the example schema from section 1 to allow the user
748 to update the artistid (the parent key of the foreign key constraint)
749 column without breaking referential integrity:
750 </p><div class="codeblock"><pre><i>-- Database schema</i>
751 CREATE TABLE artist(
752 artistid INTEGER PRIMARY KEY,
753 artistname TEXT
754 );
755 CREATE TABLE track(
756 trackid INTEGER,
757 trackname TEXT,
758 trackartist INTEGER REFERENCES artist(artistid) <b>ON UPDATE CASCADE</b>
759 );
760
761 sqlite&gt; SELECT * FROM artist;
762 artistid artistname
763 -------- -----------------
764 1 Dean Martin
765 2 Frank Sinatra
766
767 sqlite&gt; SELECT * FROM track;
768 trackid trackname trackartist
769 ------- ----------------- -----------
770 11 That's Amore 1
771 12 Christmas Blues 1
772 13 My Way 2
773
774 sqlite&gt; <i>-- Update the artistid column of the artist record for "Dean Martin".</i>
775 sqlite&gt; <i>-- Normally, this would raise a constraint, as it would orphan the two</i>
776 sqlite&gt; <i>-- dependent records in the track table. However, the ON UPDATE CASCADE clause</i>
777 sqlite&gt; <i>-- attached to the foreign key definition causes the update to "cascade"</i>
778 sqlite&gt; <i>-- to the child table, preventing the foreign key constraint violation.</i>
779 sqlite&gt; UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin';
780
781 sqlite&gt; SELECT * FROM artist;
782 artistid artistname
783 -------- -----------------
784 2 Frank Sinatra
785 100 Dean Martin
786
787 sqlite&gt; SELECT * FROM track;
788 trackid trackname trackartist
789 ------- ----------------- -----------
790 11 That's Amore 100
791 12 Christmas Blues 100
792 13 My Way 2
793 </pre></div>
794
795 <p>
796 Configuring an ON UPDATE or ON DELETE action does not mean that the foreign
797 key constraint does not need to be satisfied. For example, if an
798 "ON DELETE SET DEFAULT" action is configured,
799 but there is no row in the parent table
800 that corresponds to the default values of the child key columns, deleting
801 a parent key while dependent child keys exist still causes a foreign key
802 violation. For example:
803
804 </p><div class="codeblock"><pre><i>-- Database schema</i>
805 CREATE TABLE artist(
806 artistid INTEGER PRIMARY KEY,
807 artistname TEXT
808 );
809 CREATE TABLE track(
810 trackid INTEGER,
811 trackname TEXT,
812 trackartist INTEGER <b>DEFAULT 0</b> REFERENCES artist(artistid) <b>ON DELETE SET DEFAULT</b>
813 );
814
815 sqlite&gt; SELECT * FROM artist;
816 artistid artistname
817 -------- -----------------
818 3 Sammy Davis Jr.
819
820 sqlite&gt; SELECT * FROM track;
821 trackid trackname trackartist
822 ------- ----------------- -----------
823 14 Mr. Bojangles 3
824
825 sqlite&gt; <i>-- Deleting the row from the parent table causes the child key</i>
826 sqlite&gt; <i>-- value of the dependent row to be set to integer value 0. However, this</i>
827 sqlite&gt; <i>-- value does not correspond to any row in the parent table. Therefore</i>
828 sqlite&gt; <i>-- the foreign key constraint is violated and an is exception thrown.</i>
829 sqlite&gt; DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
830 <b>SQL error: foreign key constraint failed</b>
831
832 sqlite&gt; <i>-- This time, the value 0 does correspond to a parent table row. And</i>
833 sqlite&gt; <i>-- so the DELETE statement does not violate the foreign key constraint</i>
834 sqlite&gt; <i>-- and no exception is thrown.</i>
835 sqlite&gt; INSERT INTO artist VALUES(0, 'Unknown Artist');
836 sqlite&gt; DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
837
838 sqlite&gt; SELECT * FROM artist;
839 artistid artistname
840 -------- -----------------
841 0 Unknown Artist
842
843 sqlite&gt; SELECT * FROM track;
844 trackid trackname trackartist
845 ------- ----------------- -----------
846 14 Mr. Bojangles 0
847 </pre></div>
848
849 <p>
850 Those familiar with <a href="lang_createtrigger.html">SQLite triggers</a>
851 will have noticed that the
852 "ON DELETE SET DEFAULT" action demonstrated in the example above is
853 similar in effect to the following AFTER DELETE trigger:
854 </p><div class="codeblock"><pre>CREATE TRIGGER on_delete_set_default AFTER DELETE ON artist BEGIN
855 UPDATE child SET trackartist = 0 WHERE trackartist = old.artistid;
856 END;
857 </pre></div>
858
859 <p>
860 Whenever a row in the parent table of a foreign key constraint is deleted,
861 or when the values stored in the parent key column or columns are modified,
862 the logical sequence of events is:
863
864 </p><ol>
865 <li> Execute applicable BEFORE trigger programs,
866 </li><li> Check local (non foreign key) constraints,
867 </li><li> Update or delete the row in the parent table,
868 </li><li> Perform any required foreign key actions,
869 </li><li> Execute applicable AFTER trigger programs.
870 </li></ol>
871
872 <p>
873 There is one important difference between ON UPDATE foreign key actions and
874 SQL triggers. An ON UPDATE action is only taken if the values of the
875 parent key are modified so that the new parent key values are
876 not equal to the old. For example:
877
878 </p><div class="codeblock"><pre><i>-- Database schema</i>
879 CREATE TABLE parent(x PRIMARY KEY);
880 CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL);
881
882 sqlite&gt; SELECT * FROM parent;
883 x
884 ----
885 key
886
887 sqlite&gt; SELECT * FROM child;
888 y
889 ----
890 key
891
892 sqlite&gt; <i>-- Since the following UPDATE statement does not actually modify</i>
893 sqlite&gt; <i>-- the parent key value, the ON UPDATE action is not performed and</i>
894 sqlite&gt; <i>-- the child key value is not set to NULL.</i>
895 sqlite&gt; UPDATE parent SET x = 'key';
896 sqlite&gt; SELECT IFNULL(y, 'null') FROM child;
897 y
898 ----
899 key
900
901 sqlite&gt; <i>-- This time, since the UPDATE statement does modify the parent key</i>
902 sqlite&gt; <i>-- value, the ON UPDATE action is performed and the child key is set</i>
903 sqlite&gt; <i>-- to NULL.</i>
904 sqlite&gt; UPDATE parent SET x = 'key2';
905 sqlite&gt; SELECT IFNULL(y, 'null') FROM child;
906 y
907 ----
908 null
909 </pre></div>
910
911 <h1 id="fk_schemacommands"><span>5. </span>CREATE, ALTER and DROP TABLE commands</h1>
912
913 <p>
914 This section describes the way the <a href="lang_createtable.html">CREATE TABLE</a>, <a href="lang_altertable.html">ALTER TABLE</a>,
915 and <a href="lang_droptable.html">DROP TABLE</a> commands
916 interact with SQLite's foreign keys.
917
918 </p><p>
919 A <a href="lang_createtable.html">CREATE TABLE</a> command operates the same whether or not
920 <a href="foreignkeys.html#fk_enable">foreign key constraints are enabled</a>. The parent key definitions of
921 foreign key constraints are not checked when a table is created. There is
922 nothing stopping the user from creating a foreign key definition that
923 refers to a parent table that does not exist, or to parent key columns that
924 do not exist or are not collectively bound by a PRIMARY KEY or UNIQUE
925 constraint.
926
927 </p><p>
928 The <a href="lang_altertable.html">ALTER TABLE</a> command works differently in two respects when foreign
929 key constraints are enabled:
930
931 </p><ul>
932 <li><p>
933 It is not possible to use the "ALTER TABLE ... ADD COLUMN" syntax
934 to add a column that includes a REFERENCES clause, unless the default
935 value of the new column is NULL. Attempting to do so returns an
936 error.
937
938 </p></li><li><p>
939 If an "ALTER TABLE ... RENAME TO" command is used to rename a table
940 that is the parent table of one or more foreign key constraints, the
941 definitions of the foreign key constraints are modified to refer to
942 the parent table by its new name. The text of the child CREATE
943 TABLE statement or statements stored in the <a href="schematab.html">sqlite_schema table</a> are
944 modified to reflect the new parent table name.
945 </p></li></ul>
946
947 <p>
948 If foreign key constraints are enabled when it is prepared, the
949 <a href="lang_droptable.html">DROP TABLE</a> command performs an implicit <a href="lang_delete.html">DELETE</a> to remove all
950 rows from the table before dropping it. The implicit DELETE does not cause
951 any SQL triggers to fire, but may invoke foreign key actions or constraint
952 violations. If an immediate foreign key constraint is violated, the DROP
953 TABLE statement fails and the table is not dropped. If a deferred foreign
954 key constraint is violated, then an error is reported when the user attempts
955 to commit the transaction if the foreign key constraint violations still
956 exist at that point. Any "foreign key mismatch" errors encountered as part
957 of an implicit DELETE are ignored.
958
959 </p><p>
960 The intent of these enhancements to the <a href="lang_altertable.html">ALTER TABLE</a> and <a href="lang_droptable.html">DROP TABLE</a>
961 commands is to ensure that they cannot be used to create a database that
962 contains foreign key violations, at least while foreign key constraints are
963 enabled. There is one exception to this rule though. If a parent key is
964 not subject to a PRIMARY KEY or UNIQUE constraint created as part of the
965 parent table definition, but is subject to a UNIQUE constraint by virtue
966 of an index created using the <a href="lang_createindex.html">CREATE INDEX</a> command, then the child
967 table may be populated without causing a "foreign key mismatch" error. If
968 the UNIQUE index is dropped from the database schema, then the parent table
969 itself is dropped, no error will be reported. However the database may be
970 left in a state where the child table of the foreign key constraint contains
971 rows that do not refer to any parent table row. This case can be avoided
972 if all parent keys in the database schema are constrained by PRIMARY KEY
973 or UNIQUE constraints added as part of the parent table definition, not
974 by external UNIQUE indexes.
975
976 </p><p>
977 The properties of the <a href="lang_droptable.html">DROP TABLE</a> and <a href="lang_altertable.html">ALTER TABLE</a> commands described
978 above only apply if foreign keys are enabled. If the user considers them
979 undesirable, then the workaround is to use <a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys</a> to
980 disable foreign key constraints before executing the DROP or ALTER TABLE
981 command. Of course, while foreign key constraints are disabled, there is nothing
982 to stop the user from violating foreign key constraints and thus creating
983 an internally inconsistent database.
984
985
986
987 </p><h1 id="fk_unsupported"><span>6. </span>Limits and Unsupported Features</h1>
988
989 <p>
990 This section lists a few limitations and omitted features that are not
991 mentioned elsewhere.
992
993 </p><ol>
994 <li><p>
995 <b>No support for the MATCH clause.</b> According to SQL92, a MATCH clause
996 may be attached to a composite foreign key definition to modify the way
997 NULL values that occur in child keys are handled. If "MATCH SIMPLE" is
998 specified, then a child key is not required to correspond to any row
999 of the parent table if one or more of the child key values are NULL.
1000 If "MATCH FULL" is specified, then if any of the child key values is
1001 NULL, no corresponding row in the parent table is required, but all
1002 child key values must be NULL. Finally, if the foreign key constraint
1003 is declared as "MATCH PARTIAL" and one of the child key values is NULL,
1004 there must exist at least one row in the parent table for which the
1005 non-NULL child key values match the parent key values.
1006
1007 </p><p>
1008 SQLite parses MATCH clauses (i.e. does not report a syntax error
1009 if you specify one), but does not enforce them. All foreign key
1010 constraints in SQLite are handled as if MATCH SIMPLE were specified.
1011 </p></li><li> <p>
1012 <b>No support for switching constraints between deferred and immediate
1013 mode.</b> Many systems allow the user to toggle individual foreign key
1014 constraints between <a href="#fk_deferred">deferred</a> and immediate
1015 mode at runtime (for example using the Oracle "SET CONSTRAINT" command).
1016 SQLite does not support this. In SQLite, a foreign key constraint is
1017 permanently marked as deferred or immediate when it is created.
1018
1019 </p></li><li><p>
1020 <b>Recursion limit on foreign key actions.</b> The
1021 <a href="limits.html#max_trigger_depth">SQLITE_MAX_TRIGGER_DEPTH</a> and <a href="c3ref/c_limit_attached.html#sqlitelimittriggerdepth">SQLITE_LIMIT_TRIGGER_DEPTH</a>
1022 settings determine the maximum allowable depth of trigger
1023 program recursion. For the purposes of these limits,
1024 <a href="foreignkeys.html#fk_actions">foreign key actions</a> are considered trigger programs. The
1025 <a href="pragma.html#pragma_recursive_triggers">PRAGMA recursive_triggers</a> setting does not affect the operation
1026 of foreign key actions. It is not possible to disable recursive foreign
1027 key actions.
1028
1029 </p></li><li><p>
1030 <b>Foreign keys may not cross schema boundaries.</b> That is, in
1031 <code>REFERENCES (X.Y)</code> table <code>X</code> will only be resolved
1032 within the schema which contains the <code>REFERENCES</code> clause.
1033 </p></li></ol>
1034 <p align="center"><small><i>This page was last updated on 2026-03-20 12:37:00Z </small></i></p>
1035