Documentation Source Text

Check-in [7889bfe143]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Change foreignkeys.in to be more similar to other documentation files.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7889bfe143a5d53339e94ed131caf2ef165c4995
User & Date: dan 2010-01-21 06:06:29.000
Context
2010-01-21
09:12
Modify the search script so that it does not index the header at the top of each page or generated TOCs. (check-in: 9a1f417e0b user: dan tags: trunk)
06:06
Change foreignkeys.in to be more similar to other documentation files. (check-in: 7889bfe143 user: dan tags: trunk)
2010-01-20
18:32
Change the way the fts3.html document is generated to be more similar to most of the other documents. (check-in: e3ae335670 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/foreignkeys.in.
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
<title>SQLite Foreign Key Support</title>
<tcl>

hd_keywords {foreign key constraints} {foreign key constraint}
source [file join $::DOC pages fancyformat.tcl]
fancyformat_document "SQLite Foreign Key Support" {} {


<h2 style="margin-left:1.0em"> Overview</h2>

<p>This document describes the support for SQL foreign key constraints
   introduced in SQLite version 3.6.19. 

<p>The first section introduces the 
   concept of an SQL foreign key by example and defines the terminology 
   used for the remainder of the document. Section 2 describes the steps 
   an application must take in order to enable foreign key constraints in 
   SQLite (it is disabled by default). The next section, section 3, 
   describes the indexes that the user must create in order to use 
   foreign key constraints, and those that should be created in order for
   foreign key constraints to function efficiently. Section 4 describes
   the advanced foreign key related features supported by SQLite and
   section 5 describes the way the \[ALTER\] and \[DROP TABLE\] commands are
   enhanced to support foreign key constraints. Finally, section 6 
   enumerates the missing features and limits of the current implementation.

<p>This document does not contain a full description of the syntax used
   to create foreign key constraints in SQLite. This may be found as 
   part of the documentation for the \[CREATE TABLE\] statement.

[h1 "Introduction to Foreign Key Constraints" fk_basics]

  <p>
    SQL foreign key constraints are used to enforce "exists" relationships 
    between tables. For example, consider a database schema created using 
    the following SQL commands:

[Code {
  CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY, 
    artistname  TEXT
  );
  CREATE TABLE track(
    trackid     INTEGER,
    trackname   TEXT, 
    trackartist INTEGER     <b>-- Must map to an artist.artistid!</b>
  );
}]


  <p>
    The applications using this database are entitled to assume that for
    each row in the <i>track</i> table there exists a corresponding row in the
    <i>artist</i> table. After all, the comment in the declaration says so.
    Unfortunately, if a user edits the database using an external tool or 
    if there is a bug in an application, rows might be inserted into the 
    <i>track</i> table that do not correspond to any row in the <i>artist</i>
    table. Or rows might be deleted from the <i>artist</i> table, leaving
    orphaned rows in the <i>track</i> table that do not correspond to any of
    the remaining rows in <i>artist</i>. This might cause the application
    or applications to malfunction later on, or at least make coding the
    application more difficult.

  <p>
    One solution is to add an SQL foreign key constraint to the database 
    schema to enforce the relationship between the <i>artist</i> and 
    <i>track</i> table. ^(To do so, a foreign key definition may be added
    by modifying the declaration of the <i>track</i> table to the following:

[Code {
  CREATE TABLE track(
    trackid     INTEGER, 
    trackname   TEXT, 
    trackartist INTEGER,
    <b>FOREIGN KEY(trackartist) REFERENCES artist(artistid)</b>
  );
}])^

  <p>
    This way, the constraint is enforced by SQLite. ^(Attempting to insert
    a row into the <i>track</i> table that does not correspond to any
    row in the <i>artist</i> table will fail,)^ ^(as will attempting to
    delete a row from the <i>artist</i> table when there exist dependent 
    rows in the <i>track</i> table)^ ^There is one exception: if the foreign
    key column in the <i>track</i> table is NULL, then no corresponding
    entry in the <i>artist</i> table is required. ^(Expressed in SQL, this
    means that for every row in the <i>track</i> table, the following
    expression evaluates to true:
[Code {
  trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
}])^

  <p>^Tip: If the application requires a stricter relationship between 
     <i>artist</i> and <i>track</i>, where NULL values are not permitted 
     in the <i>trackartist</i> column, simply add the appropriate 
     "NOT NULL" constraint to the schema.

  <p>There are several other ways to add an equivalent foreign key declaration 
     to a \[CREATE TABLE\] statement. Refer to the 
     \[CREATE TABLE|CREATE TABLE documentation\] for details.

  <p>^(The following SQLite command-line session illustrates the effect of the
     foreign key constraint added to the <i>track</i> table:

[Code {
  sqlite&gt; SELECT * FROM artist;
  artistid  artistname       
  --------  -----------------
  1         Dean Martin      
  2         Frank Sinatra    

  sqlite> SELECT * FROM track;
<
<
<
|
|
|
>

|













|





|

|






|









<
>




















|






|











|

|







|
|




|










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



<tcl>hd_keywords {foreign key constraints} {foreign key constraint}</tcl>

<title>SQLite Foreign Key Support</title>
<table_of_contents>

<h2 style="margin-left:1.0em" notoc> Overview</h2>

<p>This document describes the support for SQL foreign key constraints
   introduced in SQLite version 3.6.19. 

<p>The first section introduces the 
   concept of an SQL foreign key by example and defines the terminology 
   used for the remainder of the document. Section 2 describes the steps 
   an application must take in order to enable foreign key constraints in 
   SQLite (it is disabled by default). The next section, section 3, 
   describes the indexes that the user must create in order to use 
   foreign key constraints, and those that should be created in order for
   foreign key constraints to function efficiently. Section 4 describes
   the advanced foreign key related features supported by SQLite and
   section 5 describes the way the [ALTER] and [DROP TABLE] commands are
   enhanced to support foreign key constraints. Finally, section 6 
   enumerates the missing features and limits of the current implementation.

<p>This document does not contain a full description of the syntax used
   to create foreign key constraints in SQLite. This may be found as 
   part of the documentation for the [CREATE TABLE] statement.

<h1 id=fk_basics>Introduction to Foreign Key Constraints</h1>

  <p>
    SQL foreign key constraints are used to enforce "exists" relationships 
    between tables. For example, consider a database schema created using 
    the following SQL commands:

<codeblock>
  CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY, 
    artistname  TEXT
  );
  CREATE TABLE track(
    trackid     INTEGER,
    trackname   TEXT, 
    trackartist INTEGER     <b>-- Must map to an artist.artistid!</b>
  );

</codeblock>

  <p>
    The applications using this database are entitled to assume that for
    each row in the <i>track</i> table there exists a corresponding row in the
    <i>artist</i> table. After all, the comment in the declaration says so.
    Unfortunately, if a user edits the database using an external tool or 
    if there is a bug in an application, rows might be inserted into the 
    <i>track</i> table that do not correspond to any row in the <i>artist</i>
    table. Or rows might be deleted from the <i>artist</i> table, leaving
    orphaned rows in the <i>track</i> table that do not correspond to any of
    the remaining rows in <i>artist</i>. This might cause the application
    or applications to malfunction later on, or at least make coding the
    application more difficult.

  <p>
    One solution is to add an SQL foreign key constraint to the database 
    schema to enforce the relationship between the <i>artist</i> and 
    <i>track</i> table. ^(To do so, a foreign key definition may be added
    by modifying the declaration of the <i>track</i> table to the following:

<codeblock>
  CREATE TABLE track(
    trackid     INTEGER, 
    trackname   TEXT, 
    trackartist INTEGER,
    <b>FOREIGN KEY(trackartist) REFERENCES artist(artistid)</b>
  );
</codeblock>)^

  <p>
    This way, the constraint is enforced by SQLite. ^(Attempting to insert
    a row into the <i>track</i> table that does not correspond to any
    row in the <i>artist</i> table will fail,)^ ^(as will attempting to
    delete a row from the <i>artist</i> table when there exist dependent 
    rows in the <i>track</i> table)^ ^There is one exception: if the foreign
    key column in the <i>track</i> table is NULL, then no corresponding
    entry in the <i>artist</i> table is required. ^(Expressed in SQL, this
    means that for every row in the <i>track</i> table, the following
    expression evaluates to true:
<codeblock>
  trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
</codeblock>)^

  <p>^Tip: If the application requires a stricter relationship between 
     <i>artist</i> and <i>track</i>, where NULL values are not permitted 
     in the <i>trackartist</i> column, simply add the appropriate 
     "NOT NULL" constraint to the schema.

  <p>There are several other ways to add an equivalent foreign key declaration 
     to a [CREATE TABLE] statement. Refer to the 
     [CREATE TABLE|CREATE TABLE documentation] for details.

  <p>^(The following SQLite command-line session illustrates the effect of the
     foreign key constraint added to the <i>track</i> table:

<codeblock>
  sqlite&gt; SELECT * FROM artist;
  artistid  artistname       
  --------  -----------------
  1         Dean Martin      
  2         Frank Sinatra    

  sqlite> SELECT * FROM track;
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
  sqlite&gt; INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
  sqlite&gt; UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';

  sqlite&gt; <i>-- Now that "Sammy Davis Jr." (artistid = 3) has been added to the database,</i>
  sqlite&gt; <i>-- it is possible to INSERT new tracks using this artist without violating</i>
  sqlite&gt; <i>-- the foreign key constraint:</i>
  sqlite&gt; INSERT INTO track VALUES(15, 'Boogie Woogie', 3);
}])^

  <p>^(
    As you would expect, it is not possible to manipulate the database to a state
    that violates the foreign key constraint by deleting or updating rows in the 
    <i>artist</i> table either:
[Code {
  sqlite&gt; <i>-- Attempting to delete the artist record for "Frank Sinatra" fails, since</i>
  sqlite&gt; <i>-- the track table contains a row that refer to it.</i>
  sqlite&gt; DELETE FROM artist WHERE artistname = 'Frank Sinatra';
  <b>SQL error: foreign key constraint failed</b>

  sqlite&gt; <i>-- Delete all the records from the track table that refer to the artist</i>
  sqlite&gt; <i>-- "Frank Sinatra". Only then is it possible to delete the artist.</i>
  sqlite&gt; DELETE FROM track WHERE trackname = 'My Way';
  sqlite&gt; DELETE FROM artist WHERE artistname = 'Frank Sinatra';

  sqlite&gt; <i>-- Try to update the artistid of a row in the artist table while there</i>
  sqlite&gt; <i>-- exists records in the track table that refer to it. </i>
  sqlite&gt; UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
  <b>SQL error: foreign key constraint failed</b>

  sqlite&gt; <i>-- Once all the records that refer to a row in the artist table have</i>
  sqlite&gt; <i>-- been deleted, it is possible to modify the artistid of the row.</i>
  sqlite&gt; DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');
  sqlite&gt; UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
}])^

  <p>
    SQLite uses the following terminology:

[fancyformat_fragment parentchild {parent table} {child table} {parent key} {child key}]

  <ul>
    <li><p>The <b>parent table</b> is the table that a foreign key constraint
        refers to. The parent table in the example in this section is the
        <i>artist</i> table. Some books and articles refer to this as the
        <i>referenced table</i>, which is arguably more correct, but tends
        to lead to confusion.








|





|



















|



>
|
>







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
  sqlite&gt; INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
  sqlite&gt; UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';

  sqlite&gt; <i>-- Now that "Sammy Davis Jr." (artistid = 3) has been added to the database,</i>
  sqlite&gt; <i>-- it is possible to INSERT new tracks using this artist without violating</i>
  sqlite&gt; <i>-- the foreign key constraint:</i>
  sqlite&gt; INSERT INTO track VALUES(15, 'Boogie Woogie', 3);
</codeblock>)^

  <p>^(
    As you would expect, it is not possible to manipulate the database to a state
    that violates the foreign key constraint by deleting or updating rows in the 
    <i>artist</i> table either:
<codeblock>
  sqlite&gt; <i>-- Attempting to delete the artist record for "Frank Sinatra" fails, since</i>
  sqlite&gt; <i>-- the track table contains a row that refer to it.</i>
  sqlite&gt; DELETE FROM artist WHERE artistname = 'Frank Sinatra';
  <b>SQL error: foreign key constraint failed</b>

  sqlite&gt; <i>-- Delete all the records from the track table that refer to the artist</i>
  sqlite&gt; <i>-- "Frank Sinatra". Only then is it possible to delete the artist.</i>
  sqlite&gt; DELETE FROM track WHERE trackname = 'My Way';
  sqlite&gt; DELETE FROM artist WHERE artistname = 'Frank Sinatra';

  sqlite&gt; <i>-- Try to update the artistid of a row in the artist table while there</i>
  sqlite&gt; <i>-- exists records in the track table that refer to it. </i>
  sqlite&gt; UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
  <b>SQL error: foreign key constraint failed</b>

  sqlite&gt; <i>-- Once all the records that refer to a row in the artist table have</i>
  sqlite&gt; <i>-- been deleted, it is possible to modify the artistid of the row.</i>
  sqlite&gt; DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');
  sqlite&gt; UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
</codeblock>)^

  <p>
    SQLite uses the following terminology:
<tcl>
  hd_fragment parentchild {parent table} {child table} {parent key} {child key}
</tcl>
  <ul>
    <li><p>The <b>parent table</b> is the table that a foreign key constraint
        refers to. The parent table in the example in this section is the
        <i>artist</i> table. Some books and articles refer to this as the
        <i>referenced table</i>, which is arguably more correct, but tends
        to lead to confusion.

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
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297

298
299
300
301
302
303
304

  <p>
    ^(In the above paragraph, the term "equal" means equal when values are 
    compared using the rules <a href="datatype3.html#comparisons">specified 
    here</a>.)^ The following clarifications apply:
    
  <ul>
    <li><p>^When comparing text values, the \[collating sequence\]
        associated with the parent key column is always used.
    <li><p>^When comparing values, if the parent key column has an \[affinity\],
        then that affinity is applied to the child key value before the
        comparison is performed.  
  </ul>


[h1 "Enabling Foreign Key Support" fk_enable {foreign key constraints are enabled}]

  <p>
    ^In order to use foreign key constraints in SQLite, the library must
    be compiled with neither \[SQLITE_OMIT_FOREIGN_KEY\] or 
    \[SQLITE_OMIT_TRIGGER\] defined. ^(If SQLITE_OMIT_TRIGGER is defined 
    but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior 
    to version 3.6.19 - foreign key definitions are parsed and may be 
    queried using \[PRAGMA foreign_key_list\], but foreign key constraints 
    are not enforced.)^ ^The \[PRAGMA foreign_keys\] command is a no-op in this 
    configuration. ^If OMIT_FOREIGN_KEY is defined, then foreign key 
    definitions cannot even be parsed (attempting to specify a foreign
    key definition is a syntax error).

  <p>
    ^Assuming the library is compiled with foreign key constraints enabled,
    it must still be enabled by the application at runtime, using the
    \[PRAGMA foreign_keys\] command. For example:

[Code {
  sqlite&gt; PRAGMA foreign_keys = ON;
}]


  <p>
    ^Foreign key constraints are disabled by default 
    (for backwards compatibility),
    so must be enabled separately for each \[database connection\]
    separately.
    (Note, however, that future releases of SQLite might change
    so that foreign key constraints enabled by default.  Careful
    developers will not
    make any assumptions about whether or not foreign keys are enabled by
    default but will instead enable or disable them as necessary.)
    ^The application can can also use a \[PRAGMA foreign_keys\] statement to
    determine if foreign keys are currently enabled. ^(The following 
    command-line session demonstrates this:
[Code {
  sqlite> PRAGMA foreign_keys;
  0
  sqlite> PRAGMA foreign_keys = ON;
  sqlite> PRAGMA foreign_keys;
  1
  sqlite> PRAGMA foreign_keys = OFF;
  sqlite> PRAGMA foreign_keys;
  0
}])^

  <p>^(Tip: If the command "PRAGMA foreign_keys" returns no data instead of a
     single row containing "0" or "1", then the version of SQLite you are
     using does not support foreign keys (either because it is older than
     3.6.19 or because it was compiled with \[SQLITE_OMIT_FOREIGN_KEY\] or 
     \[SQLITE_OMIT_TRIGGER\] defined).)^

  <p>
    ^(It is not possible to enable or disable foreign key constraints
    in the middle of a \[BEGIN|multi-statement transaction\] (when SQLite
    is not in \[autocommit mode\]).  Attempting to do so does not return
    an error; it simply has no effect.)^

[h1 "Required and Suggested Database Indexes" fk_indexes]

  <p>
    ^(Usually, the parent key of a foreign key constraint is the primary key of
    the parent table. If they are not the primary key, then the parent key
    columns must be collectively subject to a UNIQUE constraint or have
    a UNIQUE index.)^ 
    ^If the parent key columns have a UNIQUE index,
    then that index must use the collation sequences that are specified
    in the CREATE TABLE statement for the parent table.
    ^(For example,

[Code {
  CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);
  CREATE UNIQUE INDEX i1 ON parent(c, d);
  CREATE INDEX i2 ON parent(e);
  CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase);

  CREATE TABLE child1(f, g REFERENCES parent(a));                        <i>-- Ok</i>
  CREATE TABLE child2(h, i REFERENCES parent(b));                        <i>-- Ok</i>
  CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d));  <i>-- Ok</i>
  CREATE TABLE child4(l, m REFERENCES parent(e));                        <i>-- Error!</i>
  CREATE TABLE child5(n, o REFERENCES parent(f));                        <i>-- Error!</i>
  CREATE TABLE child6(p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c));  <i>-- Error!</i>
  CREATE TABLE child7(r REFERENCES parent(c));                           <i>-- Error!</i>
}]


  <p>
    The foreign key constraints created as part of tables <i>child1</i>, 
    <i>child2</i> and <i>child3</i> are all fine.)^ ^(The foreign key
    declared as part of table <i>child4</i> is an error because even though
    the parent key column is indexed, the index is not UNIQUE.)^
    ^(The foreign key for table <i>child5</i>







|

|




>
|
>


|
|


|
|







|

|

<
>




|






|


|








|




|
|



|
|


|











|












<
>







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
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298

299
300
301
302
303
304
305
306

  <p>
    ^(In the above paragraph, the term "equal" means equal when values are 
    compared using the rules <a href="datatype3.html#comparisons">specified 
    here</a>.)^ The following clarifications apply:
    
  <ul>
    <li><p>^When comparing text values, the [collating sequence]
        associated with the parent key column is always used.
    <li><p>^When comparing values, if the parent key column has an [affinity],
        then that affinity is applied to the child key value before the
        comparison is performed.  
  </ul>

<h1 id=fk_enable tags="foreign key constraints are enabled">
  Enabling Foreign Key Support
</h1>
  <p>
    ^In order to use foreign key constraints in SQLite, the library must
    be compiled with neither [SQLITE_OMIT_FOREIGN_KEY] or 
    [SQLITE_OMIT_TRIGGER] defined. ^(If SQLITE_OMIT_TRIGGER is defined 
    but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior 
    to version 3.6.19 - foreign key definitions are parsed and may be 
    queried using [PRAGMA foreign_key_list], but foreign key constraints 
    are not enforced.)^ ^The [PRAGMA foreign_keys] command is a no-op in this 
    configuration. ^If OMIT_FOREIGN_KEY is defined, then foreign key 
    definitions cannot even be parsed (attempting to specify a foreign
    key definition is a syntax error).

  <p>
    ^Assuming the library is compiled with foreign key constraints enabled,
    it must still be enabled by the application at runtime, using the
    [PRAGMA foreign_keys] command. For example:

<codeblock>
  sqlite&gt; PRAGMA foreign_keys = ON;

</codeblock>

  <p>
    ^Foreign key constraints are disabled by default 
    (for backwards compatibility),
    so must be enabled separately for each [database connection]
    separately.
    (Note, however, that future releases of SQLite might change
    so that foreign key constraints enabled by default.  Careful
    developers will not
    make any assumptions about whether or not foreign keys are enabled by
    default but will instead enable or disable them as necessary.)
    ^The application can can also use a [PRAGMA foreign_keys] statement to
    determine if foreign keys are currently enabled. ^(The following 
    command-line session demonstrates this:
<codeblock>
  sqlite> PRAGMA foreign_keys;
  0
  sqlite> PRAGMA foreign_keys = ON;
  sqlite> PRAGMA foreign_keys;
  1
  sqlite> PRAGMA foreign_keys = OFF;
  sqlite> PRAGMA foreign_keys;
  0
</codeblock>)^

  <p>^(Tip: If the command "PRAGMA foreign_keys" returns no data instead of a
     single row containing "0" or "1", then the version of SQLite you are
     using does not support foreign keys (either because it is older than
     3.6.19 or because it was compiled with [SQLITE_OMIT_FOREIGN_KEY] or 
     [SQLITE_OMIT_TRIGGER] defined).)^

  <p>
    ^(It is not possible to enable or disable foreign key constraints
    in the middle of a [BEGIN|multi-statement transaction] (when SQLite
    is not in [autocommit mode]).  Attempting to do so does not return
    an error; it simply has no effect.)^

<h1 id=fk_indexes>Required and Suggested Database Indexes</h1>

  <p>
    ^(Usually, the parent key of a foreign key constraint is the primary key of
    the parent table. If they are not the primary key, then the parent key
    columns must be collectively subject to a UNIQUE constraint or have
    a UNIQUE index.)^ 
    ^If the parent key columns have a UNIQUE index,
    then that index must use the collation sequences that are specified
    in the CREATE TABLE statement for the parent table.
    ^(For example,

<codeblock>
  CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);
  CREATE UNIQUE INDEX i1 ON parent(c, d);
  CREATE INDEX i2 ON parent(e);
  CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase);

  CREATE TABLE child1(f, g REFERENCES parent(a));                        <i>-- Ok</i>
  CREATE TABLE child2(h, i REFERENCES parent(b));                        <i>-- Ok</i>
  CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d));  <i>-- Ok</i>
  CREATE TABLE child4(l, m REFERENCES parent(e));                        <i>-- Error!</i>
  CREATE TABLE child5(n, o REFERENCES parent(f));                        <i>-- Error!</i>
  CREATE TABLE child6(p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c));  <i>-- Error!</i>
  CREATE TABLE child7(r REFERENCES parent(c));                           <i>-- Error!</i>

</codeblock>

  <p>
    The foreign key constraints created as part of tables <i>child1</i>, 
    <i>child2</i> and <i>child3</i> are all fine.)^ ^(The foreign key
    declared as part of table <i>child4</i> is an error because even though
    the parent key column is indexed, the index is not UNIQUE.)^
    ^(The foreign key for table <i>child5</i>
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369

370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444

445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
         specifying the primary key columns and the number of primary key
         columns in the parent do not match the number of child key columns.
  </ul>)^

  <p>
    ^(The last bullet above is illustrated by the following:

[Code {
  CREATE TABLE parent2(a, b, PRIMARY KEY(a,b));

  CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2);        <i>-- Ok</i>
  CREATE TABLE child9(x REFERENCES parent2);                             <i>-- Error!</i>
  CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2);    <i>-- Error!</i>
}])^

  <p>
    ^By contrast, if foreign key errors can be recognized simply by looking
    at the definition of the child table and without having to consult the
    parent table definition, then the 
    \[CREATE TABLE\] statement for the child table fails.  Because the error
    occurs during a schema change, this is a DDL error.
    ^Foreign key DDL errors are reported regardless of
    whether or not foreign key constraints are enabled when the
    table is created.

  <p>
    ^(Indices are not required for child key columns)^ but they are almost
    always beneficial. ^(Returning to
    the example in <a href=#fk_basics>section 1</a>, each time an application
    deletes a row from the <i>artist</i> table (the parent table), it
    performs the equivalent of the following SELECT statement to search
    for referencing rows in the <i>track</i> table (the child table).

[Code {
  SELECT rowid FROM track WHERE trackartist = ?
}]


 <p>
   where ? in the above is replaced with the value of the <i>artistid</i>
   column of the record being deleted from the <i>artist</i> table (recall
   that the <i>trackartist</i> column is the child key and the <i>artistid</i>
   column is the parent key).)^ ^(Or, more generally:

[Code {
  SELECT rowid FROM &lt;child-table&gt; WHERE &lt;child-key&gt; = :parent_key_value
}])^

 <p>
   ^If this SELECT returns any rows at all, then SQLite concludes that
   deleting the row from the parent table would violate the foreign key
   constraint and returns an error.
   ^Similar queries may be run if the content of the parent key
   is modified or a new row is inserted into the parent table.
   If these queries cannot use an index, they are forced to do a
   linear scan of the entire child table.  In a non-trivial database, this may
   be prohibitively expensive.

 <p>
   So, in most real systems, an index should be created on the child key columns
   of each foreign key constraint. ^The child key index does not have
   to be (and usually will not be) a UNIQUE index.
   Returning again to the example in section 1, ^(the
   complete database schema for efficient implementation of the foreign key 
   constraint might be:

[Code {
  CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY, 
    artistname  TEXT
  );
  CREATE TABLE track(
    trackid     INTEGER,
    trackname   TEXT, 
    trackartist INTEGER REFERENCES artist
  );
  CREATE INDEX trackindex ON track(trackartist);
}])^

  <p>
    The block above uses a shorthand form to create the foreign key constraint.
    ^Attaching a "REFERENCES <i>&lt;parent-table&gt;</i>" clause to a column
    definition creates a foreign key constraint that maps the column to the
    primary key of <i>&lt;parent-table&gt;</i>. Refer to the \[CREATE TABLE\] 
    documentation for further details.


[h1 "Advanced Foreign Key Constraint Features" fk_advanced]

[h2 "Composite Foreign Key Constraints" fk_composite]

  <p>
    A composite foreign key constraint is one where the child and parent keys
    are both composite keys. ^(For example, consider
    the following database schema:

[Code {
  CREATE TABLE album(
    albumartist TEXT,
    albumname TEXT,
    albumcover BINARY,
    PRIMARY KEY(albumartist, albumname)
  );

  CREATE TABLE song(
    songid     INTEGER,
    songartist TEXT,
    songalbum TEXT,
    songname   TEXT,
    <b>FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)</b>
  );
}]


  <p>
    In this system, each entry in the song table is required to map to an entry
    in the album table with the same combination of artist and album.)^

  <p>
    ^Parent and child keys must have the same cardinality.
    ^In SQLite, if any of the child key columns (in this case songartist 
    and songalbum) are NULL, then there is no requirement for a corresponding
    row in the parent table.

[h2 "Deferred Foreign Key Constraints" fk_deferred]

  <p>
    ^Each foreign key constraint in SQLite is classified as either immediate
     or deferred. ^Foreign key constraints are immediate by default.
     All the foreign key examples presented
     so far have been of immediate foreign key constraints.

  <p>
    ^If a statement modifies the contents of the database so that an immediate 
    foreign key constraint is in violation at the conclusion the statement, 
    an exception is thrown and 
    the effects of the statement are reverted. ^By contrast, if
    a statement modifies the contents of the database such that a deferred 
    foreign key constraint is violated, the violation is not reported
    immediately. ^Deferred foreign key constraints are not checked
    until the transaction tries to \[COMMIT\].
    ^For as long as the user has 
    an open transaction, the database is allowed to exist in a state that 
    violates any number of deferred foreign key constraints. ^However, 
    \[COMMIT\] will fail as long as foreign key constraints remain in
    violation.

  <p>
    ^(If the current statement is not inside an explicit transaction (a 
    \[BEGIN\]/\[COMMIT\]/\[ROLLBACK\] block), then an implicit
    transaction is committed
    as soon as the statement has finished executing. In this case deferred
    constraints behave the same as immediate constraints.)^

  <p>
    ^(To mark a foreign key constraint as deferred, its declaration must
    include the following clause:

[Code {
  DEFERRABLE INITIALLY DEFERRED                <i>-- A deferred foreign key constraint</i>
}])^

  <p>
    The full syntax for specifying foreign key constraints is available as part
    of the \[CREATE TABLE\] documentation. ^(Replacing the phrase above
    with any of the following 
    creates an immediate foreign key constraint.

[Code {
  NOT DEFERRABLE INITIALLY DEFERRED            <i>-- An immediate foreign key constraint</i>
  NOT DEFERRABLE INITIALLY IMMEDIATE           <i>-- An immediate foreign key constraint</i>
  NOT DEFERRABLE                               <i>-- An immediate foreign key constraint</i>
  DEFERRABLE INITIALLY IMMEDIATE               <i>-- An immediate foreign key constraint</i>
  DEFERRABLE                                   <i>-- An immediate foreign key constraint</i>
}])^

  <p>
    ^(The following example illustrates the effect of using a deferred foreign
    key constraint.

[Code {
  <i>-- Database schema. Both tables are initially empty. </i>
  CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY, 
    artistname  TEXT
  );
  CREATE TABLE track(
    trackid     INTEGER,







|





|





|













|

<
>







|

|



















|










|





|



|

|






|














<
>











|















|



|




|








|

|



|



|





|





|







336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370

371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445

446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
         specifying the primary key columns and the number of primary key
         columns in the parent do not match the number of child key columns.
  </ul>)^

  <p>
    ^(The last bullet above is illustrated by the following:

<codeblock>
  CREATE TABLE parent2(a, b, PRIMARY KEY(a,b));

  CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2);        <i>-- Ok</i>
  CREATE TABLE child9(x REFERENCES parent2);                             <i>-- Error!</i>
  CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2);    <i>-- Error!</i>
</codeblock>)^

  <p>
    ^By contrast, if foreign key errors can be recognized simply by looking
    at the definition of the child table and without having to consult the
    parent table definition, then the 
    [CREATE TABLE] statement for the child table fails.  Because the error
    occurs during a schema change, this is a DDL error.
    ^Foreign key DDL errors are reported regardless of
    whether or not foreign key constraints are enabled when the
    table is created.

  <p>
    ^(Indices are not required for child key columns)^ but they are almost
    always beneficial. ^(Returning to
    the example in <a href=#fk_basics>section 1</a>, each time an application
    deletes a row from the <i>artist</i> table (the parent table), it
    performs the equivalent of the following SELECT statement to search
    for referencing rows in the <i>track</i> table (the child table).

<codeblock>
  SELECT rowid FROM track WHERE trackartist = ?

</codeblock>

 <p>
   where ? in the above is replaced with the value of the <i>artistid</i>
   column of the record being deleted from the <i>artist</i> table (recall
   that the <i>trackartist</i> column is the child key and the <i>artistid</i>
   column is the parent key).)^ ^(Or, more generally:

<codeblock>
  SELECT rowid FROM &lt;child-table&gt; WHERE &lt;child-key&gt; = :parent_key_value
</codeblock>)^

 <p>
   ^If this SELECT returns any rows at all, then SQLite concludes that
   deleting the row from the parent table would violate the foreign key
   constraint and returns an error.
   ^Similar queries may be run if the content of the parent key
   is modified or a new row is inserted into the parent table.
   If these queries cannot use an index, they are forced to do a
   linear scan of the entire child table.  In a non-trivial database, this may
   be prohibitively expensive.

 <p>
   So, in most real systems, an index should be created on the child key columns
   of each foreign key constraint. ^The child key index does not have
   to be (and usually will not be) a UNIQUE index.
   Returning again to the example in section 1, ^(the
   complete database schema for efficient implementation of the foreign key 
   constraint might be:

<codeblock>
  CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY, 
    artistname  TEXT
  );
  CREATE TABLE track(
    trackid     INTEGER,
    trackname   TEXT, 
    trackartist INTEGER REFERENCES artist
  );
  CREATE INDEX trackindex ON track(trackartist);
</codeblock>)^

  <p>
    The block above uses a shorthand form to create the foreign key constraint.
    ^Attaching a "REFERENCES <i>&lt;parent-table&gt;</i>" clause to a column
    definition creates a foreign key constraint that maps the column to the
    primary key of <i>&lt;parent-table&gt;</i>. Refer to the [CREATE TABLE] 
    documentation for further details.


<h1 id=fk_advanced>Advanced Foreign Key Constraint Features</h1>

<h2 id=fk_composite>Composite Foreign Key Constraints</h2>

  <p>
    A composite foreign key constraint is one where the child and parent keys
    are both composite keys. ^(For example, consider
    the following database schema:

<codeblock>
  CREATE TABLE album(
    albumartist TEXT,
    albumname TEXT,
    albumcover BINARY,
    PRIMARY KEY(albumartist, albumname)
  );

  CREATE TABLE song(
    songid     INTEGER,
    songartist TEXT,
    songalbum TEXT,
    songname   TEXT,
    <b>FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)</b>
  );

</codeblock>

  <p>
    In this system, each entry in the song table is required to map to an entry
    in the album table with the same combination of artist and album.)^

  <p>
    ^Parent and child keys must have the same cardinality.
    ^In SQLite, if any of the child key columns (in this case songartist 
    and songalbum) are NULL, then there is no requirement for a corresponding
    row in the parent table.

<h2 id=fk_deferred>Deferred Foreign Key Constraints</h2>

  <p>
    ^Each foreign key constraint in SQLite is classified as either immediate
     or deferred. ^Foreign key constraints are immediate by default.
     All the foreign key examples presented
     so far have been of immediate foreign key constraints.

  <p>
    ^If a statement modifies the contents of the database so that an immediate 
    foreign key constraint is in violation at the conclusion the statement, 
    an exception is thrown and 
    the effects of the statement are reverted. ^By contrast, if
    a statement modifies the contents of the database such that a deferred 
    foreign key constraint is violated, the violation is not reported
    immediately. ^Deferred foreign key constraints are not checked
    until the transaction tries to [COMMIT].
    ^For as long as the user has 
    an open transaction, the database is allowed to exist in a state that 
    violates any number of deferred foreign key constraints. ^However, 
    [COMMIT] will fail as long as foreign key constraints remain in
    violation.

  <p>
    ^(If the current statement is not inside an explicit transaction (a 
    [BEGIN]/[COMMIT]/[ROLLBACK] block), then an implicit
    transaction is committed
    as soon as the statement has finished executing. In this case deferred
    constraints behave the same as immediate constraints.)^

  <p>
    ^(To mark a foreign key constraint as deferred, its declaration must
    include the following clause:

<codeblock>
  DEFERRABLE INITIALLY DEFERRED                <i>-- A deferred foreign key constraint</i>
</codeblock>)^

  <p>
    The full syntax for specifying foreign key constraints is available as part
    of the [CREATE TABLE] documentation. ^(Replacing the phrase above
    with any of the following 
    creates an immediate foreign key constraint.

<codeblock>
  NOT DEFERRABLE INITIALLY DEFERRED            <i>-- An immediate foreign key constraint</i>
  NOT DEFERRABLE INITIALLY IMMEDIATE           <i>-- An immediate foreign key constraint</i>
  NOT DEFERRABLE                               <i>-- An immediate foreign key constraint</i>
  DEFERRABLE INITIALLY IMMEDIATE               <i>-- An immediate foreign key constraint</i>
  DEFERRABLE                                   <i>-- An immediate foreign key constraint</i>
</codeblock>)^

  <p>
    ^(The following example illustrates the effect of using a deferred foreign
    key constraint.

<codeblock>
  <i>-- Database schema. Both tables are initially empty. </i>
  CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY, 
    artistname  TEXT
  );
  CREATE TABLE track(
    trackid     INTEGER,
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557

558

559
560
561
562
563
564
565
  <b>SQL error: foreign key constraint failed</b>

  sqlite3&gt; <i>-- After inserting a row into the artist table with artistid=5, the</i>
  sqlite3&gt; <i>-- deferred foreign key constraint is satisfied. It is then possible</i>
  sqlite3&gt; <i>-- to commit the transaction without error.</i>
  sqlite3&gt;   INSERT INTO artist VALUES(5, 'Bing Crosby');
  sqlite3&gt; COMMIT;
}])^
  <p>
    ^A \[SAVEPOINT|nested savepoint\] transaction may be RELEASEd while the
    database is in a state that does not satisfy a deferred foreign key 
    constraint. ^A transaction savepoint (a non-nested savepoint that was
    opened while there was not currently an open transaction), on the 
    other hand, is subject to the same restrictions as a COMMIT - attempting 
    to RELEASE it while the database is in such a state will fail.

  <p>
    ^If a COMMIT statement (or the RELEASE of a transaction SAVEPOINT) fails
    because the database is currently in a state that violates a deferred
    foreign key constraint and there are currently
    \[SAVEPOINT|nested savepoints\], the nested savepoints remain open.



[h2 "ON DELETE and ON UPDATE Actions" fk_actions {foreign key actions}]


  <p>
    ^Foreign key ON DELETE and ON UPDATE clauses are used to configure actions
    that take place when deleting rows from the parent table (ON DELETE), or 
    modifying the parent key values of existing rows (ON UPDATE). ^A single
    foreign key constraint may have different actions configured for ON DELETE
    and ON UPDATE. Foreign key actions are similar to triggers in many ways.







|

|










|


>
|
>







537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
  <b>SQL error: foreign key constraint failed</b>

  sqlite3&gt; <i>-- After inserting a row into the artist table with artistid=5, the</i>
  sqlite3&gt; <i>-- deferred foreign key constraint is satisfied. It is then possible</i>
  sqlite3&gt; <i>-- to commit the transaction without error.</i>
  sqlite3&gt;   INSERT INTO artist VALUES(5, 'Bing Crosby');
  sqlite3&gt; COMMIT;
</codeblock>)^
  <p>
    ^A [SAVEPOINT|nested savepoint] transaction may be RELEASEd while the
    database is in a state that does not satisfy a deferred foreign key 
    constraint. ^A transaction savepoint (a non-nested savepoint that was
    opened while there was not currently an open transaction), on the 
    other hand, is subject to the same restrictions as a COMMIT - attempting 
    to RELEASE it while the database is in such a state will fail.

  <p>
    ^If a COMMIT statement (or the RELEASE of a transaction SAVEPOINT) fails
    because the database is currently in a state that violates a deferred
    foreign key constraint and there are currently
    [SAVEPOINT|nested savepoints], the nested savepoints remain open.


<h2 id=fk_actions tags="foreign key actions">
  ON DELETE and ON UPDATE Actions
</h2>

  <p>
    ^Foreign key ON DELETE and ON UPDATE clauses are used to configure actions
    that take place when deleting rows from the parent table (ON DELETE), or 
    modifying the parent key values of existing rows (ON UPDATE). ^A single
    foreign key constraint may have different actions configured for ON DELETE
    and ON UPDATE. Foreign key actions are similar to triggers in many ways.
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
      a parent key is deleted (for ON DELETE SET NULL) or modified (for ON 
      UPDATE SET NULL), the child key columns of all rows in the child table
      that mapped to the parent key are set to contain SQL NULL values.

    <li><p> <b>SET DEFAULT</b>: ^The "SET DEFAULT" actions are similar to
      "SET NULL",
      except that each of the child key columns is set to contain the columns 
      default value instead of NULL. Refer to the \[CREATE TABLE\] 
      documentation for details on how default values are assigned to table
      columns.

    <li><p> <b>CASCADE</b>: ^A "CASCADE" action propagates the delete or update
      operation on the parent key to each dependent child key. ^For an "ON 
      DELETE CASCADE" action, this means that each row in the child table that
      was associated with the deleted parent row is also deleted. ^For an "ON
      UPDATE CASCADE" action, it means that the values stored in each dependent
      child key are modified to match the new parent key values.
  </ul>

  <p>
    ^(For example, adding an "ON UPDATE CASCADE" clause to the foreign key as
    shown below enhances the example schema from section 1 to allow the user
    to update the artistid (the parent key of the foreign key constraint) 
    column without breaking referential integrity:
[Code {
  <i>-- Database schema</i>
  CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY, 
    artistname  TEXT
  );
  CREATE TABLE track(
    trackid     INTEGER,







|
















|







597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
      a parent key is deleted (for ON DELETE SET NULL) or modified (for ON 
      UPDATE SET NULL), the child key columns of all rows in the child table
      that mapped to the parent key are set to contain SQL NULL values.

    <li><p> <b>SET DEFAULT</b>: ^The "SET DEFAULT" actions are similar to
      "SET NULL",
      except that each of the child key columns is set to contain the columns 
      default value instead of NULL. Refer to the [CREATE TABLE] 
      documentation for details on how default values are assigned to table
      columns.

    <li><p> <b>CASCADE</b>: ^A "CASCADE" action propagates the delete or update
      operation on the parent key to each dependent child key. ^For an "ON 
      DELETE CASCADE" action, this means that each row in the child table that
      was associated with the deleted parent row is also deleted. ^For an "ON
      UPDATE CASCADE" action, it means that the values stored in each dependent
      child key are modified to match the new parent key values.
  </ul>

  <p>
    ^(For example, adding an "ON UPDATE CASCADE" clause to the foreign key as
    shown below enhances the example schema from section 1 to allow the user
    to update the artistid (the parent key of the foreign key constraint) 
    column without breaking referential integrity:
<codeblock>
  <i>-- Database schema</i>
  CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY, 
    artistname  TEXT
  );
  CREATE TABLE track(
    trackid     INTEGER,
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679

  sqlite&gt; SELECT * FROM track;
  trackid  trackname          trackartist
  -------  -----------------  -----------
  11       That's Amore       100
  12       Christmas Blues    100  
  13       My Way             2  
}])^

  <p>
    ^Configuring an ON UPDATE or ON DELETE action does not mean that the foreign
    key constraint does not need to be satisfied. ^For example, if an
    "ON DELETE SET DEFAULT" action is configured,
    but there is no row in the parent table 
    that corresponds to the default values of the child key columns, deleting
    a parent key while dependent child keys exist still causes a foreign key
    violation. ^(For example:

[Code {
  <i>-- Database schema</i>
  CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY, 
    artistname  TEXT
  );
  CREATE TABLE track(
    trackid     INTEGER,







|










|







658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683

  sqlite&gt; SELECT * FROM track;
  trackid  trackname          trackartist
  -------  -----------------  -----------
  11       That's Amore       100
  12       Christmas Blues    100  
  13       My Way             2  
</codeblock>)^

  <p>
    ^Configuring an ON UPDATE or ON DELETE action does not mean that the foreign
    key constraint does not need to be satisfied. ^For example, if an
    "ON DELETE SET DEFAULT" action is configured,
    but there is no row in the parent table 
    that corresponds to the default values of the child key columns, deleting
    a parent key while dependent child keys exist still causes a foreign key
    violation. ^(For example:

<codeblock>
  <i>-- Database schema</i>
  CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY, 
    artistname  TEXT
  );
  CREATE TABLE track(
    trackid     INTEGER,
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727

728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
  --------  -----------------
  0         Unknown Artist

  sqlite&gt; SELECT * FROM track;
  trackid  trackname          trackartist
  -------  -----------------  -----------
  14       Mr. Bojangles      0
}])^

  <p>
    Those familiar with \[CREATE TRIGGER | SQLite triggers\]
    will have noticed that the 
    "ON DELETE SET DEFAULT" action demonstrated in the example above is
    similar in effect to the following AFTER DELETE trigger:
[Code {
  CREATE TRIGGER on_delete_set_default AFTER DELETE ON artist BEGIN
    UPDATE child SET trackartist = 0 WHERE trackartist = old.artistid;
  END;
}]


  <p>
    ^(Whenever a row in the parent table of a foreign key constraint is deleted,
    or when the values stored in the parent key column or columns are modified, 
    the logical sequence of events is:

  <ol>
   <li> Execute applicable BEFORE trigger programs,
   <li> Check local (non foreign key) constraints,
   <li> Update or delete the row in the parent table,
   <li> Perform any required foreign key actions,
   <li> Execute applicable AFTER trigger programs.
  </ol>)^

  <p>
    There is one important difference between ON UPDATE foreign key actions and
    SQL triggers. ^An ON UPDATE action is only taken if the values of the
    parent key are modified so that the new parent key values are 
    not equal to the old. ^(For example:

[Code {
  <i>-- Database schema</i>
  CREATE TABLE parent(x PRIMARY KEY);
  CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL);

  sqlite&gt; SELECT * FROM parent;
  x
  ----







|


|



|



<
>




















|







713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730

731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
  --------  -----------------
  0         Unknown Artist

  sqlite&gt; SELECT * FROM track;
  trackid  trackname          trackartist
  -------  -----------------  -----------
  14       Mr. Bojangles      0
</codeblock>)^

  <p>
    Those familiar with [CREATE TRIGGER | SQLite triggers]
    will have noticed that the 
    "ON DELETE SET DEFAULT" action demonstrated in the example above is
    similar in effect to the following AFTER DELETE trigger:
<codeblock>
  CREATE TRIGGER on_delete_set_default AFTER DELETE ON artist BEGIN
    UPDATE child SET trackartist = 0 WHERE trackartist = old.artistid;
  END;

</codeblock>

  <p>
    ^(Whenever a row in the parent table of a foreign key constraint is deleted,
    or when the values stored in the parent key column or columns are modified, 
    the logical sequence of events is:

  <ol>
   <li> Execute applicable BEFORE trigger programs,
   <li> Check local (non foreign key) constraints,
   <li> Update or delete the row in the parent table,
   <li> Perform any required foreign key actions,
   <li> Execute applicable AFTER trigger programs.
  </ol>)^

  <p>
    There is one important difference between ON UPDATE foreign key actions and
    SQL triggers. ^An ON UPDATE action is only taken if the values of the
    parent key are modified so that the new parent key values are 
    not equal to the old. ^(For example:

<codeblock>
  <i>-- Database schema</i>
  CREATE TABLE parent(x PRIMARY KEY);
  CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL);

  sqlite&gt; SELECT * FROM parent;
  x
  ----
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
  sqlite&gt; <i>-- value, the ON UPDATE action is performed and the child key is set</i>
  sqlite&gt; <i>-- to NULL.</i>
  sqlite&gt; UPDATE parent SET x = 'key2';
  sqlite&gt; SELECT IFNULL(y, 'null') FROM child;
  y
  ----
  null
}])^

[h1 "CREATE, ALTER and DROP TABLE commands" fk_schemacommands]

  <p>
    This section describes the way the \[CREATE TABLE\], \[ALTER TABLE\],
    and \[DROP TABLE\] commands
    interact with SQLite's foreign keys.

  <p>
    ^A \[CREATE TABLE\] command operates the same whether or not
    \[foreign key constraints are enabled\].  ^The parent key definitions of 
    foreign key constraints are not checked when a table is created. ^There is
    nothing stopping the user from creating a foreign key definition that
    refers to a parent table that does not exist, or to parent key columns that
    do not exist or are not collectively bound by a PRIMARY KEY or UNIQUE constraint.

  <p>
    The \[ALTER TABLE\] command works differently in two respects when foreign
    key constraints are enabled:

  <ul>
    <li><p> 
         ^(It is not possible to use the "ALTER TABLE ... ADD COLUMN" syntax
         to add a column that includes a REFERENCES clause, unless the default
         value of the new column is NULL. Attempting to do so returns an
         error.)^

    <li><p> 
         ^(If an "ALTER TABLE ... RENAME TO" command is used to rename a table
         that is the parent table of one or more foreign key constraints, the
         definitions of the foreign key constraints are modified to refer to
         the parent table by its new name)^. ^The text of the child CREATE
         TABLE statement or statements stored in the sqlite_master table are 
         modified to reflect the new parent table name.
  </ul>

  <p>
    ^If foreign key constraints are enabled when it is prepared, the 
    \[DROP TABLE\] command performs an implicit \[DELETE\] to remove all
    rows from the table before dropping it. ^The implicit DELETE does not cause
    any SQL triggers to fire, but may invoke foreign key actions or constraint
    violations. ^If an immediate foreign key constraint is violated, the DROP
    TABLE statement fails and the table is not dropped. ^If a deferred foreign
    key constraint is violated, then an error is reported when the user attempts
    to commit the transaction if the foreign key constraint violations still
    exist at that point. ^Any "foreign key mismatch" errors encountered as part 
    of an implicit DELETE are ignored.

  <p>
    The intent of these enhancements to the \[ALTER TABLE\] and \[DROP TABLE\]
    commands is to ensure that they cannot be used to create a database that
    contains foreign key violations, at least while foreign key constraints are
    enabled. There is one exception to this rule though. If a parent key is
    not subject to a PRIMARY KEY or UNIQUE constraint created as part of the
    parent table definition, but is subject to a UNIQUE constraint by virtue
    of an index created using the \[CREATE INDEX\] command, then the child 
    table may be populated without causing a "foreign key mismatch" error. If
    the UNIQUE index is dropped from the database schema, then the parent table
    itself is dropped, no error will be reported. However the database may be
    left in a state where the child table of the foreign key constraint contains
    rows that do not refer to any parent table row. This case can be avoided
    if all parent keys in the database schema are constrained by PRIMARY KEY
    or UNIQUE constraints added as part of the parent table definition, not
    by external UNIQUE indexes.

  <p>
    ^The properties of the \[DROP TABLE\] and \[ALTER TABLE\] commands described
    above only apply if foreign keys are enabled. If the user considers them
    undesirable, then the workaround is to use \[PRAGMA foreign_keys\] to 
    disable foreign key constraints before executing the DROP or ALTER TABLE 
    command. Of course, while foreign key constraints are disabled, there is nothing
    to stop the user from violating foreign key constraints and thus creating
    an internally inconsistent database.
   
    

[h1 "Limits and Unsupported Features" fk_unsupported]

  <p>
    This section lists a few limitations and omitted features that are not
    mentioned elsewhere.

  <ol>
    <li><p> 







|

|


|
|



|
|






|




















|










|





|










|

|







|







777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
  sqlite&gt; <i>-- value, the ON UPDATE action is performed and the child key is set</i>
  sqlite&gt; <i>-- to NULL.</i>
  sqlite&gt; UPDATE parent SET x = 'key2';
  sqlite&gt; SELECT IFNULL(y, 'null') FROM child;
  y
  ----
  null
</codeblock>)^

<h1 id=fk_schemacommands>CREATE, ALTER and DROP TABLE commands</h1>

  <p>
    This section describes the way the [CREATE TABLE], [ALTER TABLE],
    and [DROP TABLE] commands
    interact with SQLite's foreign keys.

  <p>
    ^A [CREATE TABLE] command operates the same whether or not
    [foreign key constraints are enabled].  ^The parent key definitions of 
    foreign key constraints are not checked when a table is created. ^There is
    nothing stopping the user from creating a foreign key definition that
    refers to a parent table that does not exist, or to parent key columns that
    do not exist or are not collectively bound by a PRIMARY KEY or UNIQUE constraint.

  <p>
    The [ALTER TABLE] command works differently in two respects when foreign
    key constraints are enabled:

  <ul>
    <li><p> 
         ^(It is not possible to use the "ALTER TABLE ... ADD COLUMN" syntax
         to add a column that includes a REFERENCES clause, unless the default
         value of the new column is NULL. Attempting to do so returns an
         error.)^

    <li><p> 
         ^(If an "ALTER TABLE ... RENAME TO" command is used to rename a table
         that is the parent table of one or more foreign key constraints, the
         definitions of the foreign key constraints are modified to refer to
         the parent table by its new name)^. ^The text of the child CREATE
         TABLE statement or statements stored in the sqlite_master table are 
         modified to reflect the new parent table name.
  </ul>

  <p>
    ^If foreign key constraints are enabled when it is prepared, the 
    [DROP TABLE] command performs an implicit [DELETE] to remove all
    rows from the table before dropping it. ^The implicit DELETE does not cause
    any SQL triggers to fire, but may invoke foreign key actions or constraint
    violations. ^If an immediate foreign key constraint is violated, the DROP
    TABLE statement fails and the table is not dropped. ^If a deferred foreign
    key constraint is violated, then an error is reported when the user attempts
    to commit the transaction if the foreign key constraint violations still
    exist at that point. ^Any "foreign key mismatch" errors encountered as part 
    of an implicit DELETE are ignored.

  <p>
    The intent of these enhancements to the [ALTER TABLE] and [DROP TABLE]
    commands is to ensure that they cannot be used to create a database that
    contains foreign key violations, at least while foreign key constraints are
    enabled. There is one exception to this rule though. If a parent key is
    not subject to a PRIMARY KEY or UNIQUE constraint created as part of the
    parent table definition, but is subject to a UNIQUE constraint by virtue
    of an index created using the [CREATE INDEX] command, then the child 
    table may be populated without causing a "foreign key mismatch" error. If
    the UNIQUE index is dropped from the database schema, then the parent table
    itself is dropped, no error will be reported. However the database may be
    left in a state where the child table of the foreign key constraint contains
    rows that do not refer to any parent table row. This case can be avoided
    if all parent keys in the database schema are constrained by PRIMARY KEY
    or UNIQUE constraints added as part of the parent table definition, not
    by external UNIQUE indexes.

  <p>
    ^The properties of the [DROP TABLE] and [ALTER TABLE] commands described
    above only apply if foreign keys are enabled. If the user considers them
    undesirable, then the workaround is to use [PRAGMA foreign_keys] to 
    disable foreign key constraints before executing the DROP or ALTER TABLE 
    command. Of course, while foreign key constraints are disabled, there is nothing
    to stop the user from violating foreign key constraints and thus creating
    an internally inconsistent database.
   
    

<h1 id=fk_unsupported>Limits and Unsupported Features</h1>

  <p>
    This section lists a few limitations and omitted features that are not
    mentioned elsewhere.

  <ol>
    <li><p> 
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
         constraints between <a href=#fk_deferred>deferred</a> and immediate 
         mode at runtime (for example using the Oracle "SET CONSTRAINT" command).
         SQLite does not support this. ^In SQLite, a foreign key constraint is
         permanently marked as deferred or immediate when it is created.

    <li><p>
      <b>Recursion limit on foreign key actions.</b> ^(The 
         \[SQLITE_MAX_TRIGGER_DEPTH\] and \[SQLITE_LIMIT_TRIGGER_DEPTH\]
         settings determine the maximum allowable depth of trigger
         program recursion. For the purposes of these limits,
         \[foreign key actions\] are considered trigger programs.)^ ^The
         \[PRAGMA recursive_triggers\] setting does not not affect the operation 
         of foreign key actions. It is not possible to disable recursive foreign 
         key actions.
   </ol>
}







|


|
|



<
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902

         constraints between <a href=#fk_deferred>deferred</a> and immediate 
         mode at runtime (for example using the Oracle "SET CONSTRAINT" command).
         SQLite does not support this. ^In SQLite, a foreign key constraint is
         permanently marked as deferred or immediate when it is created.

    <li><p>
      <b>Recursion limit on foreign key actions.</b> ^(The 
         [SQLITE_MAX_TRIGGER_DEPTH] and [SQLITE_LIMIT_TRIGGER_DEPTH]
         settings determine the maximum allowable depth of trigger
         program recursion. For the purposes of these limits,
         [foreign key actions] are considered trigger programs.)^ ^The
         [PRAGMA recursive_triggers] setting does not not affect the operation 
         of foreign key actions. It is not possible to disable recursive foreign 
         key actions.
   </ol>