Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| SHA1 Hash: | 7889bfe143a5d53339e94ed131caf2ef165c4995 |
|---|---|
| Date: | 2010-01-21 06:06:29 |
| User: | dan |
| Comment: | Change foreignkeys.in to be more similar to other documentation files. |
Tags And Properties
- branch=trunk inherited from [b2e03e19ab]
- sym-trunk inherited from [b2e03e19ab]
Changes
Changes to pages/foreignkeys.in
> 1 <tcl>hd_keywords {foreign key constraints} {foreign key constraint}</tcl> > 2 1 <title>SQLite Foreign Key Support</title> 3 <title>SQLite Foreign Key Support</title> 2 <tcl> | 4 <table_of_contents> 3 5 4 hd_keywords {foreign key constraints} {foreign key constraint} < 5 source [file join $::DOC pages fancyformat.tcl] < 6 fancyformat_document "SQLite Foreign Key Support" {} { < 7 < 8 <h2 style="margin-left:1.0em"> Overview</h2> | 6 <h2 style="margin-left:1.0em" notoc> Overview</h2> 9 7 10 <p>This document describes the support for SQL foreign key constraints 8 <p>This document describes the support for SQL foreign key constraints 11 introduced in SQLite version 3.6.19. 9 introduced in SQLite version 3.6.19. 12 10 13 <p>The first section introduces the 11 <p>The first section introduces the 14 concept of an SQL foreign key by example and defines the terminology 12 concept of an SQL foreign key by example and defines the terminology 15 used for the remainder of the document. Section 2 describes the steps 13 used for the remainder of the document. Section 2 describes the steps 16 an application must take in order to enable foreign key constraints in 14 an application must take in order to enable foreign key constraints in 17 SQLite (it is disabled by default). The next section, section 3, 15 SQLite (it is disabled by default). The next section, section 3, 18 describes the indexes that the user must create in order to use 16 describes the indexes that the user must create in order to use 19 foreign key constraints, and those that should be created in order for 17 foreign key constraints, and those that should be created in order for 20 foreign key constraints to function efficiently. Section 4 describes 18 foreign key constraints to function efficiently. Section 4 describes 21 the advanced foreign key related features supported by SQLite and 19 the advanced foreign key related features supported by SQLite and 22 section 5 describes the way the \[ALTER\] and \[DROP TABLE\] commands are | 20 section 5 describes the way the [ALTER] and [DROP TABLE] commands are 23 enhanced to support foreign key constraints. Finally, section 6 21 enhanced to support foreign key constraints. Finally, section 6 24 enumerates the missing features and limits of the current implementation. 22 enumerates the missing features and limits of the current implementation. 25 23 26 <p>This document does not contain a full description of the syntax used 24 <p>This document does not contain a full description of the syntax used 27 to create foreign key constraints in SQLite. This may be found as 25 to create foreign key constraints in SQLite. This may be found as 28 part of the documentation for the \[CREATE TABLE\] statement. | 26 part of the documentation for the [CREATE TABLE] statement. 29 27 30 [h1 "Introduction to Foreign Key Constraints" fk_basics] | 28 <h1 id=fk_basics>Introduction to Foreign Key Constraints</h1> 31 29 32 <p> 30 <p> 33 SQL foreign key constraints are used to enforce "exists" relationships 31 SQL foreign key constraints are used to enforce "exists" relationships 34 between tables. For example, consider a database schema created using 32 between tables. For example, consider a database schema created using 35 the following SQL commands: 33 the following SQL commands: 36 34 37 [Code { | 35 <codeblock> 38 CREATE TABLE artist( 36 CREATE TABLE artist( 39 artistid INTEGER PRIMARY KEY, 37 artistid INTEGER PRIMARY KEY, 40 artistname TEXT 38 artistname TEXT 41 ); 39 ); 42 CREATE TABLE track( 40 CREATE TABLE track( 43 trackid INTEGER, 41 trackid INTEGER, 44 trackname TEXT, 42 trackname TEXT, 45 trackartist INTEGER <b>-- Must map to an artist.artistid!</b> 43 trackartist INTEGER <b>-- Must map to an artist.artistid!</b> 46 ); 44 ); 47 }] < > 45 </codeblock> 48 46 49 <p> 47 <p> 50 The applications using this database are entitled to assume that for 48 The applications using this database are entitled to assume that for 51 each row in the <i>track</i> table there exists a corresponding row in the 49 each row in the <i>track</i> table there exists a corresponding row in the 52 <i>artist</i> table. After all, the comment in the declaration says so. 50 <i>artist</i> table. After all, the comment in the declaration says so. 53 Unfortunately, if a user edits the database using an external tool or 51 Unfortunately, if a user edits the database using an external tool or 54 if there is a bug in an application, rows might be inserted into the 52 if there is a bug in an application, rows might be inserted into the ................................................................................................................................................................................ 61 59 62 <p> 60 <p> 63 One solution is to add an SQL foreign key constraint to the database 61 One solution is to add an SQL foreign key constraint to the database 64 schema to enforce the relationship between the <i>artist</i> and 62 schema to enforce the relationship between the <i>artist</i> and 65 <i>track</i> table. ^(To do so, a foreign key definition may be added 63 <i>track</i> table. ^(To do so, a foreign key definition may be added 66 by modifying the declaration of the <i>track</i> table to the following: 64 by modifying the declaration of the <i>track</i> table to the following: 67 65 68 [Code { | 66 <codeblock> 69 CREATE TABLE track( 67 CREATE TABLE track( 70 trackid INTEGER, 68 trackid INTEGER, 71 trackname TEXT, 69 trackname TEXT, 72 trackartist INTEGER, 70 trackartist INTEGER, 73 <b>FOREIGN KEY(trackartist) REFERENCES artist(artistid)</b> 71 <b>FOREIGN KEY(trackartist) REFERENCES artist(artistid)</b> 74 ); 72 ); 75 }])^ | 73 </codeblock>)^ 76 74 77 <p> 75 <p> 78 This way, the constraint is enforced by SQLite. ^(Attempting to insert 76 This way, the constraint is enforced by SQLite. ^(Attempting to insert 79 a row into the <i>track</i> table that does not correspond to any 77 a row into the <i>track</i> table that does not correspond to any 80 row in the <i>artist</i> table will fail,)^ ^(as will attempting to 78 row in the <i>artist</i> table will fail,)^ ^(as will attempting to 81 delete a row from the <i>artist</i> table when there exist dependent 79 delete a row from the <i>artist</i> table when there exist dependent 82 rows in the <i>track</i> table)^ ^There is one exception: if the foreign 80 rows in the <i>track</i> table)^ ^There is one exception: if the foreign 83 key column in the <i>track</i> table is NULL, then no corresponding 81 key column in the <i>track</i> table is NULL, then no corresponding 84 entry in the <i>artist</i> table is required. ^(Expressed in SQL, this 82 entry in the <i>artist</i> table is required. ^(Expressed in SQL, this 85 means that for every row in the <i>track</i> table, the following 83 means that for every row in the <i>track</i> table, the following 86 expression evaluates to true: 84 expression evaluates to true: 87 [Code { | 85 <codeblock> 88 trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist) 86 trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist) 89 }])^ | 87 </codeblock>)^ 90 88 91 <p>^Tip: If the application requires a stricter relationship between 89 <p>^Tip: If the application requires a stricter relationship between 92 <i>artist</i> and <i>track</i>, where NULL values are not permitted 90 <i>artist</i> and <i>track</i>, where NULL values are not permitted 93 in the <i>trackartist</i> column, simply add the appropriate 91 in the <i>trackartist</i> column, simply add the appropriate 94 "NOT NULL" constraint to the schema. 92 "NOT NULL" constraint to the schema. 95 93 96 <p>There are several other ways to add an equivalent foreign key declaration 94 <p>There are several other ways to add an equivalent foreign key declaration 97 to a \[CREATE TABLE\] statement. Refer to the | 95 to a [CREATE TABLE] statement. Refer to the 98 \[CREATE TABLE|CREATE TABLE documentation\] for details. | 96 [CREATE TABLE|CREATE TABLE documentation] for details. 99 97 100 <p>^(The following SQLite command-line session illustrates the effect of the 98 <p>^(The following SQLite command-line session illustrates the effect of the 101 foreign key constraint added to the <i>track</i> table: 99 foreign key constraint added to the <i>track</i> table: 102 100 103 [Code { | 101 <codeblock> 104 sqlite> SELECT * FROM artist; 102 sqlite> SELECT * FROM artist; 105 artistid artistname 103 artistid artistname 106 -------- ----------------- 104 -------- ----------------- 107 1 Dean Martin 105 1 Dean Martin 108 2 Frank Sinatra 106 2 Frank Sinatra 109 107 110 sqlite> SELECT * FROM track; 108 sqlite> SELECT * FROM track; ................................................................................................................................................................................ 135 sqlite> INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); 133 sqlite> INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); 136 sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; 134 sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; 137 135 138 sqlite> <i>-- Now that "Sammy Davis Jr." (artistid = 3) has been added to t 136 sqlite> <i>-- Now that "Sammy Davis Jr." (artistid = 3) has been added to t 139 sqlite> <i>-- it is possible to INSERT new tracks using this artist without 137 sqlite> <i>-- it is possible to INSERT new tracks using this artist without 140 sqlite> <i>-- the foreign key constraint:</i> 138 sqlite> <i>-- the foreign key constraint:</i> 141 sqlite> INSERT INTO track VALUES(15, 'Boogie Woogie', 3); 139 sqlite> INSERT INTO track VALUES(15, 'Boogie Woogie', 3); 142 }])^ | 140 </codeblock>)^ 143 141 144 <p>^( 142 <p>^( 145 As you would expect, it is not possible to manipulate the database to a stat 143 As you would expect, it is not possible to manipulate the database to a stat 146 that violates the foreign key constraint by deleting or updating rows in the 144 that violates the foreign key constraint by deleting or updating rows in the 147 <i>artist</i> table either: 145 <i>artist</i> table either: 148 [Code { | 146 <codeblock> 149 sqlite> <i>-- Attempting to delete the artist record for "Frank Sinatra" fa 147 sqlite> <i>-- Attempting to delete the artist record for "Frank Sinatra" fa 150 sqlite> <i>-- the track table contains a row that refer to it.</i> 148 sqlite> <i>-- the track table contains a row that refer to it.</i> 151 sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra'; 149 sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra'; 152 <b>SQL error: foreign key constraint failed</b> 150 <b>SQL error: foreign key constraint failed</b> 153 151 154 sqlite> <i>-- Delete all the records from the track table that refer to the 152 sqlite> <i>-- Delete all the records from the track table that refer to the 155 sqlite> <i>-- "Frank Sinatra". Only then is it possible to delete the artis 153 sqlite> <i>-- "Frank Sinatra". Only then is it possible to delete the artis ................................................................................................................................................................................ 161 sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; 159 sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; 162 <b>SQL error: foreign key constraint failed</b> 160 <b>SQL error: foreign key constraint failed</b> 163 161 164 sqlite> <i>-- Once all the records that refer to a row in the artist table 162 sqlite> <i>-- Once all the records that refer to a row in the artist table 165 sqlite> <i>-- been deleted, it is possible to modify the artistid of the ro 163 sqlite> <i>-- been deleted, it is possible to modify the artistid of the ro 166 sqlite> DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Bl 164 sqlite> DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Bl 167 sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; 165 sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; 168 }])^ | 166 </codeblock>)^ 169 167 170 <p> 168 <p> 171 SQLite uses the following terminology: 169 SQLite uses the following terminology: > 170 <tcl> 172 [fancyformat_fragment parentchild {parent table} {child table} {parent key} {chi | 171 hd_fragment parentchild {parent table} {child table} {parent key} {child key} > 172 </tcl> 173 <ul> 173 <ul> 174 <li><p>The <b>parent table</b> is the table that a foreign key constraint 174 <li><p>The <b>parent table</b> is the table that a foreign key constraint 175 refers to. The parent table in the example in this section is the 175 refers to. The parent table in the example in this section is the 176 <i>artist</i> table. Some books and articles refer to this as the 176 <i>artist</i> table. Some books and articles refer to this as the 177 <i>referenced table</i>, which is arguably more correct, but tends 177 <i>referenced table</i>, which is arguably more correct, but tends 178 to lead to confusion. 178 to lead to confusion. 179 179 ................................................................................................................................................................................ 200 200 201 <p> 201 <p> 202 ^(In the above paragraph, the term "equal" means equal when values are 202 ^(In the above paragraph, the term "equal" means equal when values are 203 compared using the rules <a href="datatype3.html#comparisons">specified 203 compared using the rules <a href="datatype3.html#comparisons">specified 204 here</a>.)^ The following clarifications apply: 204 here</a>.)^ The following clarifications apply: 205 205 206 <ul> 206 <ul> 207 <li><p>^When comparing text values, the \[collating sequence\] | 207 <li><p>^When comparing text values, the [collating sequence] 208 associated with the parent key column is always used. 208 associated with the parent key column is always used. 209 <li><p>^When comparing values, if the parent key column has an \[affinity\], | 209 <li><p>^When comparing values, if the parent key column has an [affinity], 210 then that affinity is applied to the child key value before the 210 then that affinity is applied to the child key value before the 211 comparison is performed. 211 comparison is performed. 212 </ul> 212 </ul> 213 213 > 214 <h1 id=fk_enable tags="foreign key constraints are enabled"> 214 [h1 "Enabling Foreign Key Support" fk_enable {foreign key constraints are enable | 215 Enabling Foreign Key Support > 216 </h1> 215 <p> 217 <p> 216 ^In order to use foreign key constraints in SQLite, the library must 218 ^In order to use foreign key constraints in SQLite, the library must 217 be compiled with neither \[SQLITE_OMIT_FOREIGN_KEY\] or | 219 be compiled with neither [SQLITE_OMIT_FOREIGN_KEY] or 218 \[SQLITE_OMIT_TRIGGER\] defined. ^(If SQLITE_OMIT_TRIGGER is defined | 220 [SQLITE_OMIT_TRIGGER] defined. ^(If SQLITE_OMIT_TRIGGER is defined 219 but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior 221 but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior 220 to version 3.6.19 - foreign key definitions are parsed and may be 222 to version 3.6.19 - foreign key definitions are parsed and may be 221 queried using \[PRAGMA foreign_key_list\], but foreign key constraints | 223 queried using [PRAGMA foreign_key_list], but foreign key constraints 222 are not enforced.)^ ^The \[PRAGMA foreign_keys\] command is a no-op in this | 224 are not enforced.)^ ^The [PRAGMA foreign_keys] command is a no-op in this 223 configuration. ^If OMIT_FOREIGN_KEY is defined, then foreign key 225 configuration. ^If OMIT_FOREIGN_KEY is defined, then foreign key 224 definitions cannot even be parsed (attempting to specify a foreign 226 definitions cannot even be parsed (attempting to specify a foreign 225 key definition is a syntax error). 227 key definition is a syntax error). 226 228 227 <p> 229 <p> 228 ^Assuming the library is compiled with foreign key constraints enabled, 230 ^Assuming the library is compiled with foreign key constraints enabled, 229 it must still be enabled by the application at runtime, using the 231 it must still be enabled by the application at runtime, using the 230 \[PRAGMA foreign_keys\] command. For example: | 232 [PRAGMA foreign_keys] command. For example: 231 233 232 [Code { | 234 <codeblock> 233 sqlite> PRAGMA foreign_keys = ON; 235 sqlite> PRAGMA foreign_keys = ON; 234 }] < > 236 </codeblock> 235 237 236 <p> 238 <p> 237 ^Foreign key constraints are disabled by default 239 ^Foreign key constraints are disabled by default 238 (for backwards compatibility), 240 (for backwards compatibility), 239 so must be enabled separately for each \[database connection\] | 241 so must be enabled separately for each [database connection] 240 separately. 242 separately. 241 (Note, however, that future releases of SQLite might change 243 (Note, however, that future releases of SQLite might change 242 so that foreign key constraints enabled by default. Careful 244 so that foreign key constraints enabled by default. Careful 243 developers will not 245 developers will not 244 make any assumptions about whether or not foreign keys are enabled by 246 make any assumptions about whether or not foreign keys are enabled by 245 default but will instead enable or disable them as necessary.) 247 default but will instead enable or disable them as necessary.) 246 ^The application can can also use a \[PRAGMA foreign_keys\] statement to | 248 ^The application can can also use a [PRAGMA foreign_keys] statement to 247 determine if foreign keys are currently enabled. ^(The following 249 determine if foreign keys are currently enabled. ^(The following 248 command-line session demonstrates this: 250 command-line session demonstrates this: 249 [Code { | 251 <codeblock> 250 sqlite> PRAGMA foreign_keys; 252 sqlite> PRAGMA foreign_keys; 251 0 253 0 252 sqlite> PRAGMA foreign_keys = ON; 254 sqlite> PRAGMA foreign_keys = ON; 253 sqlite> PRAGMA foreign_keys; 255 sqlite> PRAGMA foreign_keys; 254 1 256 1 255 sqlite> PRAGMA foreign_keys = OFF; 257 sqlite> PRAGMA foreign_keys = OFF; 256 sqlite> PRAGMA foreign_keys; 258 sqlite> PRAGMA foreign_keys; 257 0 259 0 258 }])^ | 260 </codeblock>)^ 259 261 260 <p>^(Tip: If the command "PRAGMA foreign_keys" returns no data instead of a 262 <p>^(Tip: If the command "PRAGMA foreign_keys" returns no data instead of a 261 single row containing "0" or "1", then the version of SQLite you are 263 single row containing "0" or "1", then the version of SQLite you are 262 using does not support foreign keys (either because it is older than 264 using does not support foreign keys (either because it is older than 263 3.6.19 or because it was compiled with \[SQLITE_OMIT_FOREIGN_KEY\] or | 265 3.6.19 or because it was compiled with [SQLITE_OMIT_FOREIGN_KEY] or 264 \[SQLITE_OMIT_TRIGGER\] defined).)^ | 266 [SQLITE_OMIT_TRIGGER] defined).)^ 265 267 266 <p> 268 <p> 267 ^(It is not possible to enable or disable foreign key constraints 269 ^(It is not possible to enable or disable foreign key constraints 268 in the middle of a \[BEGIN|multi-statement transaction\] (when SQLite | 270 in the middle of a [BEGIN|multi-statement transaction] (when SQLite 269 is not in \[autocommit mode\]). Attempting to do so does not return | 271 is not in [autocommit mode]). Attempting to do so does not return 270 an error; it simply has no effect.)^ 272 an error; it simply has no effect.)^ 271 273 272 [h1 "Required and Suggested Database Indexes" fk_indexes] | 274 <h1 id=fk_indexes>Required and Suggested Database Indexes</h1> 273 275 274 <p> 276 <p> 275 ^(Usually, the parent key of a foreign key constraint is the primary key of 277 ^(Usually, the parent key of a foreign key constraint is the primary key of 276 the parent table. If they are not the primary key, then the parent key 278 the parent table. If they are not the primary key, then the parent key 277 columns must be collectively subject to a UNIQUE constraint or have 279 columns must be collectively subject to a UNIQUE constraint or have 278 a UNIQUE index.)^ 280 a UNIQUE index.)^ 279 ^If the parent key columns have a UNIQUE index, 281 ^If the parent key columns have a UNIQUE index, 280 then that index must use the collation sequences that are specified 282 then that index must use the collation sequences that are specified 281 in the CREATE TABLE statement for the parent table. 283 in the CREATE TABLE statement for the parent table. 282 ^(For example, 284 ^(For example, 283 285 284 [Code { | 286 <codeblock> 285 CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f); 287 CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f); 286 CREATE UNIQUE INDEX i1 ON parent(c, d); 288 CREATE UNIQUE INDEX i1 ON parent(c, d); 287 CREATE INDEX i2 ON parent(e); 289 CREATE INDEX i2 ON parent(e); 288 CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase); 290 CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase); 289 291 290 CREATE TABLE child1(f, g REFERENCES parent(a)); <i>-- O 292 CREATE TABLE child1(f, g REFERENCES parent(a)); <i>-- O 291 CREATE TABLE child2(h, i REFERENCES parent(b)); <i>-- O 293 CREATE TABLE child2(h, i REFERENCES parent(b)); <i>-- O 292 CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); <i>-- O 294 CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); <i>-- O 293 CREATE TABLE child4(l, m REFERENCES parent(e)); <i>-- E 295 CREATE TABLE child4(l, m REFERENCES parent(e)); <i>-- E 294 CREATE TABLE child5(n, o REFERENCES parent(f)); <i>-- E 296 CREATE TABLE child5(n, o REFERENCES parent(f)); <i>-- E 295 CREATE TABLE child6(p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c)); <i>-- E 297 CREATE TABLE child6(p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c)); <i>-- E 296 CREATE TABLE child7(r REFERENCES parent(c)); <i>-- E 298 CREATE TABLE child7(r REFERENCES parent(c)); <i>-- E 297 }] < > 299 </codeblock> 298 300 299 <p> 301 <p> 300 The foreign key constraints created as part of tables <i>child1</i>, 302 The foreign key constraints created as part of tables <i>child1</i>, 301 <i>child2</i> and <i>child3</i> are all fine.)^ ^(The foreign key 303 <i>child2</i> and <i>child3</i> are all fine.)^ ^(The foreign key 302 declared as part of table <i>child4</i> is an error because even though 304 declared as part of table <i>child4</i> is an error because even though 303 the parent key column is indexed, the index is not UNIQUE.)^ 305 the parent key column is indexed, the index is not UNIQUE.)^ 304 ^(The foreign key for table <i>child5</i> 306 ^(The foreign key for table <i>child5</i> ................................................................................................................................................................................ 334 specifying the primary key columns and the number of primary key 336 specifying the primary key columns and the number of primary key 335 columns in the parent do not match the number of child key columns. 337 columns in the parent do not match the number of child key columns. 336 </ul>)^ 338 </ul>)^ 337 339 338 <p> 340 <p> 339 ^(The last bullet above is illustrated by the following: 341 ^(The last bullet above is illustrated by the following: 340 342 341 [Code { | 343 <codeblock> 342 CREATE TABLE parent2(a, b, PRIMARY KEY(a,b)); 344 CREATE TABLE parent2(a, b, PRIMARY KEY(a,b)); 343 345 344 CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2); <i>-- O 346 CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2); <i>-- O 345 CREATE TABLE child9(x REFERENCES parent2); <i>-- E 347 CREATE TABLE child9(x REFERENCES parent2); <i>-- E 346 CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2); <i>-- E 348 CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2); <i>-- E 347 }])^ | 349 </codeblock>)^ 348 350 349 <p> 351 <p> 350 ^By contrast, if foreign key errors can be recognized simply by looking 352 ^By contrast, if foreign key errors can be recognized simply by looking 351 at the definition of the child table and without having to consult the 353 at the definition of the child table and without having to consult the 352 parent table definition, then the 354 parent table definition, then the 353 \[CREATE TABLE\] statement for the child table fails. Because the error | 355 [CREATE TABLE] statement for the child table fails. Because the error 354 occurs during a schema change, this is a DDL error. 356 occurs during a schema change, this is a DDL error. 355 ^Foreign key DDL errors are reported regardless of 357 ^Foreign key DDL errors are reported regardless of 356 whether or not foreign key constraints are enabled when the 358 whether or not foreign key constraints are enabled when the 357 table is created. 359 table is created. 358 360 359 <p> 361 <p> 360 ^(Indices are not required for child key columns)^ but they are almost 362 ^(Indices are not required for child key columns)^ but they are almost 361 always beneficial. ^(Returning to 363 always beneficial. ^(Returning to 362 the example in <a href=#fk_basics>section 1</a>, each time an application 364 the example in <a href=#fk_basics>section 1</a>, each time an application 363 deletes a row from the <i>artist</i> table (the parent table), it 365 deletes a row from the <i>artist</i> table (the parent table), it 364 performs the equivalent of the following SELECT statement to search 366 performs the equivalent of the following SELECT statement to search 365 for referencing rows in the <i>track</i> table (the child table). 367 for referencing rows in the <i>track</i> table (the child table). 366 368 367 [Code { | 369 <codeblock> 368 SELECT rowid FROM track WHERE trackartist = ? 370 SELECT rowid FROM track WHERE trackartist = ? 369 }] < > 371 </codeblock> 370 372 371 <p> 373 <p> 372 where ? in the above is replaced with the value of the <i>artistid</i> 374 where ? in the above is replaced with the value of the <i>artistid</i> 373 column of the record being deleted from the <i>artist</i> table (recall 375 column of the record being deleted from the <i>artist</i> table (recall 374 that the <i>trackartist</i> column is the child key and the <i>artistid</i> 376 that the <i>trackartist</i> column is the child key and the <i>artistid</i> 375 column is the parent key).)^ ^(Or, more generally: 377 column is the parent key).)^ ^(Or, more generally: 376 378 377 [Code { | 379 <codeblock> 378 SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_va 380 SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_va 379 }])^ | 381 </codeblock>)^ 380 382 381 <p> 383 <p> 382 ^If this SELECT returns any rows at all, then SQLite concludes that 384 ^If this SELECT returns any rows at all, then SQLite concludes that 383 deleting the row from the parent table would violate the foreign key 385 deleting the row from the parent table would violate the foreign key 384 constraint and returns an error. 386 constraint and returns an error. 385 ^Similar queries may be run if the content of the parent key 387 ^Similar queries may be run if the content of the parent key 386 is modified or a new row is inserted into the parent table. 388 is modified or a new row is inserted into the parent table. ................................................................................................................................................................................ 392 So, in most real systems, an index should be created on the child key columns 394 So, in most real systems, an index should be created on the child key columns 393 of each foreign key constraint. ^The child key index does not have 395 of each foreign key constraint. ^The child key index does not have 394 to be (and usually will not be) a UNIQUE index. 396 to be (and usually will not be) a UNIQUE index. 395 Returning again to the example in section 1, ^(the 397 Returning again to the example in section 1, ^(the 396 complete database schema for efficient implementation of the foreign key 398 complete database schema for efficient implementation of the foreign key 397 constraint might be: 399 constraint might be: 398 400 399 [Code { | 401 <codeblock> 400 CREATE TABLE artist( 402 CREATE TABLE artist( 401 artistid INTEGER PRIMARY KEY, 403 artistid INTEGER PRIMARY KEY, 402 artistname TEXT 404 artistname TEXT 403 ); 405 ); 404 CREATE TABLE track( 406 CREATE TABLE track( 405 trackid INTEGER, 407 trackid INTEGER, 406 trackname TEXT, 408 trackname TEXT, 407 trackartist INTEGER REFERENCES artist 409 trackartist INTEGER REFERENCES artist 408 ); 410 ); 409 CREATE INDEX trackindex ON track(trackartist); 411 CREATE INDEX trackindex ON track(trackartist); 410 }])^ | 412 </codeblock>)^ 411 413 412 <p> 414 <p> 413 The block above uses a shorthand form to create the foreign key constraint. 415 The block above uses a shorthand form to create the foreign key constraint. 414 ^Attaching a "REFERENCES <i><parent-table></i>" clause to a column 416 ^Attaching a "REFERENCES <i><parent-table></i>" clause to a column 415 definition creates a foreign key constraint that maps the column to the 417 definition creates a foreign key constraint that maps the column to the 416 primary key of <i><parent-table></i>. Refer to the \[CREATE TABLE\] | 418 primary key of <i><parent-table></i>. Refer to the [CREATE TABLE] 417 documentation for further details. 419 documentation for further details. 418 420 419 421 420 [h1 "Advanced Foreign Key Constraint Features" fk_advanced] | 422 <h1 id=fk_advanced>Advanced Foreign Key Constraint Features</h1> 421 423 422 [h2 "Composite Foreign Key Constraints" fk_composite] | 424 <h2 id=fk_composite>Composite Foreign Key Constraints</h2> 423 425 424 <p> 426 <p> 425 A composite foreign key constraint is one where the child and parent keys 427 A composite foreign key constraint is one where the child and parent keys 426 are both composite keys. ^(For example, consider 428 are both composite keys. ^(For example, consider 427 the following database schema: 429 the following database schema: 428 430 429 [Code { | 431 <codeblock> 430 CREATE TABLE album( 432 CREATE TABLE album( 431 albumartist TEXT, 433 albumartist TEXT, 432 albumname TEXT, 434 albumname TEXT, 433 albumcover BINARY, 435 albumcover BINARY, 434 PRIMARY KEY(albumartist, albumname) 436 PRIMARY KEY(albumartist, albumname) 435 ); 437 ); 436 438 ................................................................................................................................................................................ 437 CREATE TABLE song( 439 CREATE TABLE song( 438 songid INTEGER, 440 songid INTEGER, 439 songartist TEXT, 441 songartist TEXT, 440 songalbum TEXT, 442 songalbum TEXT, 441 songname TEXT, 443 songname TEXT, 442 <b>FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumnam 444 <b>FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumnam 443 ); 445 ); 444 }] < > 446 </codeblock> 445 447 446 <p> 448 <p> 447 In this system, each entry in the song table is required to map to an entry 449 In this system, each entry in the song table is required to map to an entry 448 in the album table with the same combination of artist and album.)^ 450 in the album table with the same combination of artist and album.)^ 449 451 450 <p> 452 <p> 451 ^Parent and child keys must have the same cardinality. 453 ^Parent and child keys must have the same cardinality. 452 ^In SQLite, if any of the child key columns (in this case songartist 454 ^In SQLite, if any of the child key columns (in this case songartist 453 and songalbum) are NULL, then there is no requirement for a corresponding 455 and songalbum) are NULL, then there is no requirement for a corresponding 454 row in the parent table. 456 row in the parent table. 455 457 456 [h2 "Deferred Foreign Key Constraints" fk_deferred] | 458 <h2 id=fk_deferred>Deferred Foreign Key Constraints</h2> 457 459 458 <p> 460 <p> 459 ^Each foreign key constraint in SQLite is classified as either immediate 461 ^Each foreign key constraint in SQLite is classified as either immediate 460 or deferred. ^Foreign key constraints are immediate by default. 462 or deferred. ^Foreign key constraints are immediate by default. 461 All the foreign key examples presented 463 All the foreign key examples presented 462 so far have been of immediate foreign key constraints. 464 so far have been of immediate foreign key constraints. 463 465 ................................................................................................................................................................................ 465 ^If a statement modifies the contents of the database so that an immediate 467 ^If a statement modifies the contents of the database so that an immediate 466 foreign key constraint is in violation at the conclusion the statement, 468 foreign key constraint is in violation at the conclusion the statement, 467 an exception is thrown and 469 an exception is thrown and 468 the effects of the statement are reverted. ^By contrast, if 470 the effects of the statement are reverted. ^By contrast, if 469 a statement modifies the contents of the database such that a deferred 471 a statement modifies the contents of the database such that a deferred 470 foreign key constraint is violated, the violation is not reported 472 foreign key constraint is violated, the violation is not reported 471 immediately. ^Deferred foreign key constraints are not checked 473 immediately. ^Deferred foreign key constraints are not checked 472 until the transaction tries to \[COMMIT\]. | 474 until the transaction tries to [COMMIT]. 473 ^For as long as the user has 475 ^For as long as the user has 474 an open transaction, the database is allowed to exist in a state that 476 an open transaction, the database is allowed to exist in a state that 475 violates any number of deferred foreign key constraints. ^However, 477 violates any number of deferred foreign key constraints. ^However, 476 \[COMMIT\] will fail as long as foreign key constraints remain in | 478 [COMMIT] will fail as long as foreign key constraints remain in 477 violation. 479 violation. 478 480 479 <p> 481 <p> 480 ^(If the current statement is not inside an explicit transaction (a 482 ^(If the current statement is not inside an explicit transaction (a 481 \[BEGIN\]/\[COMMIT\]/\[ROLLBACK\] block), then an implicit | 483 [BEGIN]/[COMMIT]/[ROLLBACK] block), then an implicit 482 transaction is committed 484 transaction is committed 483 as soon as the statement has finished executing. In this case deferred 485 as soon as the statement has finished executing. In this case deferred 484 constraints behave the same as immediate constraints.)^ 486 constraints behave the same as immediate constraints.)^ 485 487 486 <p> 488 <p> 487 ^(To mark a foreign key constraint as deferred, its declaration must 489 ^(To mark a foreign key constraint as deferred, its declaration must 488 include the following clause: 490 include the following clause: 489 491 490 [Code { | 492 <codeblock> 491 DEFERRABLE INITIALLY DEFERRED <i>-- A deferred foreign key cons 493 DEFERRABLE INITIALLY DEFERRED <i>-- A deferred foreign key cons 492 }])^ | 494 </codeblock>)^ 493 495 494 <p> 496 <p> 495 The full syntax for specifying foreign key constraints is available as part 497 The full syntax for specifying foreign key constraints is available as part 496 of the \[CREATE TABLE\] documentation. ^(Replacing the phrase above | 498 of the [CREATE TABLE] documentation. ^(Replacing the phrase above 497 with any of the following 499 with any of the following 498 creates an immediate foreign key constraint. 500 creates an immediate foreign key constraint. 499 501 500 [Code { | 502 <codeblock> 501 NOT DEFERRABLE INITIALLY DEFERRED <i>-- An immediate foreign key co 503 NOT DEFERRABLE INITIALLY DEFERRED <i>-- An immediate foreign key co 502 NOT DEFERRABLE INITIALLY IMMEDIATE <i>-- An immediate foreign key co 504 NOT DEFERRABLE INITIALLY IMMEDIATE <i>-- An immediate foreign key co 503 NOT DEFERRABLE <i>-- An immediate foreign key co 505 NOT DEFERRABLE <i>-- An immediate foreign key co 504 DEFERRABLE INITIALLY IMMEDIATE <i>-- An immediate foreign key co 506 DEFERRABLE INITIALLY IMMEDIATE <i>-- An immediate foreign key co 505 DEFERRABLE <i>-- An immediate foreign key co 507 DEFERRABLE <i>-- An immediate foreign key co 506 }])^ | 508 </codeblock>)^ 507 509 508 <p> 510 <p> 509 ^(The following example illustrates the effect of using a deferred foreign 511 ^(The following example illustrates the effect of using a deferred foreign 510 key constraint. 512 key constraint. 511 513 512 [Code { | 514 <codeblock> 513 <i>-- Database schema. Both tables are initially empty. </i> 515 <i>-- Database schema. Both tables are initially empty. </i> 514 CREATE TABLE artist( 516 CREATE TABLE artist( 515 artistid INTEGER PRIMARY KEY, 517 artistid INTEGER PRIMARY KEY, 516 artistname TEXT 518 artistname TEXT 517 ); 519 ); 518 CREATE TABLE track( 520 CREATE TABLE track( 519 trackid INTEGER, 521 trackid INTEGER, ................................................................................................................................................................................ 535 <b>SQL error: foreign key constraint failed</b> 537 <b>SQL error: foreign key constraint failed</b> 536 538 537 sqlite3> <i>-- After inserting a row into the artist table with artistid=5, 539 sqlite3> <i>-- After inserting a row into the artist table with artistid=5, 538 sqlite3> <i>-- deferred foreign key constraint is satisfied. It is then pos 540 sqlite3> <i>-- deferred foreign key constraint is satisfied. It is then pos 539 sqlite3> <i>-- to commit the transaction without error.</i> 541 sqlite3> <i>-- to commit the transaction without error.</i> 540 sqlite3> INSERT INTO artist VALUES(5, 'Bing Crosby'); 542 sqlite3> INSERT INTO artist VALUES(5, 'Bing Crosby'); 541 sqlite3> COMMIT; 543 sqlite3> COMMIT; 542 }])^ | 544 </codeblock>)^ 543 <p> 545 <p> 544 ^A \[SAVEPOINT|nested savepoint\] transaction may be RELEASEd while the | 546 ^A [SAVEPOINT|nested savepoint] transaction may be RELEASEd while the 545 database is in a state that does not satisfy a deferred foreign key 547 database is in a state that does not satisfy a deferred foreign key 546 constraint. ^A transaction savepoint (a non-nested savepoint that was 548 constraint. ^A transaction savepoint (a non-nested savepoint that was 547 opened while there was not currently an open transaction), on the 549 opened while there was not currently an open transaction), on the 548 other hand, is subject to the same restrictions as a COMMIT - attempting 550 other hand, is subject to the same restrictions as a COMMIT - attempting 549 to RELEASE it while the database is in such a state will fail. 551 to RELEASE it while the database is in such a state will fail. 550 552 551 <p> 553 <p> 552 ^If a COMMIT statement (or the RELEASE of a transaction SAVEPOINT) fails 554 ^If a COMMIT statement (or the RELEASE of a transaction SAVEPOINT) fails 553 because the database is currently in a state that violates a deferred 555 because the database is currently in a state that violates a deferred 554 foreign key constraint and there are currently 556 foreign key constraint and there are currently 555 \[SAVEPOINT|nested savepoints\], the nested savepoints remain open. | 557 [SAVEPOINT|nested savepoints], the nested savepoints remain open. 556 558 557 559 > 560 <h2 id=fk_actions tags="foreign key actions"> 558 [h2 "ON DELETE and ON UPDATE Actions" fk_actions {foreign key actions}] | 561 ON DELETE and ON UPDATE Actions > 562 </h2> 559 563 560 <p> 564 <p> 561 ^Foreign key ON DELETE and ON UPDATE clauses are used to configure actions 565 ^Foreign key ON DELETE and ON UPDATE clauses are used to configure actions 562 that take place when deleting rows from the parent table (ON DELETE), or 566 that take place when deleting rows from the parent table (ON DELETE), or 563 modifying the parent key values of existing rows (ON UPDATE). ^A single 567 modifying the parent key values of existing rows (ON UPDATE). ^A single 564 foreign key constraint may have different actions configured for ON DELETE 568 foreign key constraint may have different actions configured for ON DELETE 565 and ON UPDATE. Foreign key actions are similar to triggers in many ways. 569 and ON UPDATE. Foreign key actions are similar to triggers in many ways. ................................................................................................................................................................................ 593 a parent key is deleted (for ON DELETE SET NULL) or modified (for ON 597 a parent key is deleted (for ON DELETE SET NULL) or modified (for ON 594 UPDATE SET NULL), the child key columns of all rows in the child table 598 UPDATE SET NULL), the child key columns of all rows in the child table 595 that mapped to the parent key are set to contain SQL NULL values. 599 that mapped to the parent key are set to contain SQL NULL values. 596 600 597 <li><p> <b>SET DEFAULT</b>: ^The "SET DEFAULT" actions are similar to 601 <li><p> <b>SET DEFAULT</b>: ^The "SET DEFAULT" actions are similar to 598 "SET NULL", 602 "SET NULL", 599 except that each of the child key columns is set to contain the columns 603 except that each of the child key columns is set to contain the columns 600 default value instead of NULL. Refer to the \[CREATE TABLE\] | 604 default value instead of NULL. Refer to the [CREATE TABLE] 601 documentation for details on how default values are assigned to table 605 documentation for details on how default values are assigned to table 602 columns. 606 columns. 603 607 604 <li><p> <b>CASCADE</b>: ^A "CASCADE" action propagates the delete or update 608 <li><p> <b>CASCADE</b>: ^A "CASCADE" action propagates the delete or update 605 operation on the parent key to each dependent child key. ^For an "ON 609 operation on the parent key to each dependent child key. ^For an "ON 606 DELETE CASCADE" action, this means that each row in the child table that 610 DELETE CASCADE" action, this means that each row in the child table that 607 was associated with the deleted parent row is also deleted. ^For an "ON 611 was associated with the deleted parent row is also deleted. ^For an "ON ................................................................................................................................................................................ 610 </ul> 614 </ul> 611 615 612 <p> 616 <p> 613 ^(For example, adding an "ON UPDATE CASCADE" clause to the foreign key as 617 ^(For example, adding an "ON UPDATE CASCADE" clause to the foreign key as 614 shown below enhances the example schema from section 1 to allow the user 618 shown below enhances the example schema from section 1 to allow the user 615 to update the artistid (the parent key of the foreign key constraint) 619 to update the artistid (the parent key of the foreign key constraint) 616 column without breaking referential integrity: 620 column without breaking referential integrity: 617 [Code { | 621 <codeblock> 618 <i>-- Database schema</i> 622 <i>-- Database schema</i> 619 CREATE TABLE artist( 623 CREATE TABLE artist( 620 artistid INTEGER PRIMARY KEY, 624 artistid INTEGER PRIMARY KEY, 621 artistname TEXT 625 artistname TEXT 622 ); 626 ); 623 CREATE TABLE track( 627 CREATE TABLE track( 624 trackid INTEGER, 628 trackid INTEGER, ................................................................................................................................................................................ 654 658 655 sqlite> SELECT * FROM track; 659 sqlite> SELECT * FROM track; 656 trackid trackname trackartist 660 trackid trackname trackartist 657 ------- ----------------- ----------- 661 ------- ----------------- ----------- 658 11 That's Amore 100 662 11 That's Amore 100 659 12 Christmas Blues 100 663 12 Christmas Blues 100 660 13 My Way 2 664 13 My Way 2 661 }])^ | 665 </codeblock>)^ 662 666 663 <p> 667 <p> 664 ^Configuring an ON UPDATE or ON DELETE action does not mean that the foreign 668 ^Configuring an ON UPDATE or ON DELETE action does not mean that the foreign 665 key constraint does not need to be satisfied. ^For example, if an 669 key constraint does not need to be satisfied. ^For example, if an 666 "ON DELETE SET DEFAULT" action is configured, 670 "ON DELETE SET DEFAULT" action is configured, 667 but there is no row in the parent table 671 but there is no row in the parent table 668 that corresponds to the default values of the child key columns, deleting 672 that corresponds to the default values of the child key columns, deleting 669 a parent key while dependent child keys exist still causes a foreign key 673 a parent key while dependent child keys exist still causes a foreign key 670 violation. ^(For example: 674 violation. ^(For example: 671 675 672 [Code { | 676 <codeblock> 673 <i>-- Database schema</i> 677 <i>-- Database schema</i> 674 CREATE TABLE artist( 678 CREATE TABLE artist( 675 artistid INTEGER PRIMARY KEY, 679 artistid INTEGER PRIMARY KEY, 676 artistname TEXT 680 artistname TEXT 677 ); 681 ); 678 CREATE TABLE track( 682 CREATE TABLE track( 679 trackid INTEGER, 683 trackid INTEGER, ................................................................................................................................................................................ 709 -------- ----------------- 713 -------- ----------------- 710 0 Unknown Artist 714 0 Unknown Artist 711 715 712 sqlite> SELECT * FROM track; 716 sqlite> SELECT * FROM track; 713 trackid trackname trackartist 717 trackid trackname trackartist 714 ------- ----------------- ----------- 718 ------- ----------------- ----------- 715 14 Mr. Bojangles 0 719 14 Mr. Bojangles 0 716 }])^ | 720 </codeblock>)^ 717 721 718 <p> 722 <p> 719 Those familiar with \[CREATE TRIGGER | SQLite triggers\] | 723 Those familiar with [CREATE TRIGGER | SQLite triggers] 720 will have noticed that the 724 will have noticed that the 721 "ON DELETE SET DEFAULT" action demonstrated in the example above is 725 "ON DELETE SET DEFAULT" action demonstrated in the example above is 722 similar in effect to the following AFTER DELETE trigger: 726 similar in effect to the following AFTER DELETE trigger: 723 [Code { | 727 <codeblock> 724 CREATE TRIGGER on_delete_set_default AFTER DELETE ON artist BEGIN 728 CREATE TRIGGER on_delete_set_default AFTER DELETE ON artist BEGIN 725 UPDATE child SET trackartist = 0 WHERE trackartist = old.artistid; 729 UPDATE child SET trackartist = 0 WHERE trackartist = old.artistid; 726 END; 730 END; 727 }] < > 731 </codeblock> 728 732 729 <p> 733 <p> 730 ^(Whenever a row in the parent table of a foreign key constraint is deleted, 734 ^(Whenever a row in the parent table of a foreign key constraint is deleted, 731 or when the values stored in the parent key column or columns are modified, 735 or when the values stored in the parent key column or columns are modified, 732 the logical sequence of events is: 736 the logical sequence of events is: 733 737 734 <ol> 738 <ol> ................................................................................................................................................................................ 741 745 742 <p> 746 <p> 743 There is one important difference between ON UPDATE foreign key actions and 747 There is one important difference between ON UPDATE foreign key actions and 744 SQL triggers. ^An ON UPDATE action is only taken if the values of the 748 SQL triggers. ^An ON UPDATE action is only taken if the values of the 745 parent key are modified so that the new parent key values are 749 parent key are modified so that the new parent key values are 746 not equal to the old. ^(For example: 750 not equal to the old. ^(For example: 747 751 748 [Code { | 752 <codeblock> 749 <i>-- Database schema</i> 753 <i>-- Database schema</i> 750 CREATE TABLE parent(x PRIMARY KEY); 754 CREATE TABLE parent(x PRIMARY KEY); 751 CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL); 755 CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL); 752 756 753 sqlite> SELECT * FROM parent; 757 sqlite> SELECT * FROM parent; 754 x 758 x 755 ---- 759 ---- ................................................................................................................................................................................ 773 sqlite> <i>-- value, the ON UPDATE action is performed and the child key is 777 sqlite> <i>-- value, the ON UPDATE action is performed and the child key is 774 sqlite> <i>-- to NULL.</i> 778 sqlite> <i>-- to NULL.</i> 775 sqlite> UPDATE parent SET x = 'key2'; 779 sqlite> UPDATE parent SET x = 'key2'; 776 sqlite> SELECT IFNULL(y, 'null') FROM child; 780 sqlite> SELECT IFNULL(y, 'null') FROM child; 777 y 781 y 778 ---- 782 ---- 779 null 783 null 780 }])^ | 784 </codeblock>)^ 781 785 782 [h1 "CREATE, ALTER and DROP TABLE commands" fk_schemacommands] | 786 <h1 id=fk_schemacommands>CREATE, ALTER and DROP TABLE commands</h1> 783 787 784 <p> 788 <p> 785 This section describes the way the \[CREATE TABLE\], \[ALTER TABLE\], | 789 This section describes the way the [CREATE TABLE], [ALTER TABLE], 786 and \[DROP TABLE\] commands | 790 and [DROP TABLE] commands 787 interact with SQLite's foreign keys. 791 interact with SQLite's foreign keys. 788 792 789 <p> 793 <p> 790 ^A \[CREATE TABLE\] command operates the same whether or not | 794 ^A [CREATE TABLE] command operates the same whether or not 791 \[foreign key constraints are enabled\]. ^The parent key definitions of | 795 [foreign key constraints are enabled]. ^The parent key definitions of 792 foreign key constraints are not checked when a table is created. ^There is 796 foreign key constraints are not checked when a table is created. ^There is 793 nothing stopping the user from creating a foreign key definition that 797 nothing stopping the user from creating a foreign key definition that 794 refers to a parent table that does not exist, or to parent key columns that 798 refers to a parent table that does not exist, or to parent key columns that 795 do not exist or are not collectively bound by a PRIMARY KEY or UNIQUE constr 799 do not exist or are not collectively bound by a PRIMARY KEY or UNIQUE constr 796 800 797 <p> 801 <p> 798 The \[ALTER TABLE\] command works differently in two respects when foreign | 802 The [ALTER TABLE] command works differently in two respects when foreign 799 key constraints are enabled: 803 key constraints are enabled: 800 804 801 <ul> 805 <ul> 802 <li><p> 806 <li><p> 803 ^(It is not possible to use the "ALTER TABLE ... ADD COLUMN" syntax 807 ^(It is not possible to use the "ALTER TABLE ... ADD COLUMN" syntax 804 to add a column that includes a REFERENCES clause, unless the default 808 to add a column that includes a REFERENCES clause, unless the default 805 value of the new column is NULL. Attempting to do so returns an 809 value of the new column is NULL. Attempting to do so returns an ................................................................................................................................................................................ 812 the parent table by its new name)^. ^The text of the child CREATE 816 the parent table by its new name)^. ^The text of the child CREATE 813 TABLE statement or statements stored in the sqlite_master table are 817 TABLE statement or statements stored in the sqlite_master table are 814 modified to reflect the new parent table name. 818 modified to reflect the new parent table name. 815 </ul> 819 </ul> 816 820 817 <p> 821 <p> 818 ^If foreign key constraints are enabled when it is prepared, the 822 ^If foreign key constraints are enabled when it is prepared, the 819 \[DROP TABLE\] command performs an implicit \[DELETE\] to remove all | 823 [DROP TABLE] command performs an implicit [DELETE] to remove all 820 rows from the table before dropping it. ^The implicit DELETE does not cause 824 rows from the table before dropping it. ^The implicit DELETE does not cause 821 any SQL triggers to fire, but may invoke foreign key actions or constraint 825 any SQL triggers to fire, but may invoke foreign key actions or constraint 822 violations. ^If an immediate foreign key constraint is violated, the DROP 826 violations. ^If an immediate foreign key constraint is violated, the DROP 823 TABLE statement fails and the table is not dropped. ^If a deferred foreign 827 TABLE statement fails and the table is not dropped. ^If a deferred foreign 824 key constraint is violated, then an error is reported when the user attempts 828 key constraint is violated, then an error is reported when the user attempts 825 to commit the transaction if the foreign key constraint violations still 829 to commit the transaction if the foreign key constraint violations still 826 exist at that point. ^Any "foreign key mismatch" errors encountered as part 830 exist at that point. ^Any "foreign key mismatch" errors encountered as part 827 of an implicit DELETE are ignored. 831 of an implicit DELETE are ignored. 828 832 829 <p> 833 <p> 830 The intent of these enhancements to the \[ALTER TABLE\] and \[DROP TABLE\] | 834 The intent of these enhancements to the [ALTER TABLE] and [DROP TABLE] 831 commands is to ensure that they cannot be used to create a database that 835 commands is to ensure that they cannot be used to create a database that 832 contains foreign key violations, at least while foreign key constraints are 836 contains foreign key violations, at least while foreign key constraints are 833 enabled. There is one exception to this rule though. If a parent key is 837 enabled. There is one exception to this rule though. If a parent key is 834 not subject to a PRIMARY KEY or UNIQUE constraint created as part of the 838 not subject to a PRIMARY KEY or UNIQUE constraint created as part of the 835 parent table definition, but is subject to a UNIQUE constraint by virtue 839 parent table definition, but is subject to a UNIQUE constraint by virtue 836 of an index created using the \[CREATE INDEX\] command, then the child | 840 of an index created using the [CREATE INDEX] command, then the child 837 table may be populated without causing a "foreign key mismatch" error. If 841 table may be populated without causing a "foreign key mismatch" error. If 838 the UNIQUE index is dropped from the database schema, then the parent table 842 the UNIQUE index is dropped from the database schema, then the parent table 839 itself is dropped, no error will be reported. However the database may be 843 itself is dropped, no error will be reported. However the database may be 840 left in a state where the child table of the foreign key constraint contains 844 left in a state where the child table of the foreign key constraint contains 841 rows that do not refer to any parent table row. This case can be avoided 845 rows that do not refer to any parent table row. This case can be avoided 842 if all parent keys in the database schema are constrained by PRIMARY KEY 846 if all parent keys in the database schema are constrained by PRIMARY KEY 843 or UNIQUE constraints added as part of the parent table definition, not 847 or UNIQUE constraints added as part of the parent table definition, not 844 by external UNIQUE indexes. 848 by external UNIQUE indexes. 845 849 846 <p> 850 <p> 847 ^The properties of the \[DROP TABLE\] and \[ALTER TABLE\] commands described | 851 ^The properties of the [DROP TABLE] and [ALTER TABLE] commands described 848 above only apply if foreign keys are enabled. If the user considers them 852 above only apply if foreign keys are enabled. If the user considers them 849 undesirable, then the workaround is to use \[PRAGMA foreign_keys\] to | 853 undesirable, then the workaround is to use [PRAGMA foreign_keys] to 850 disable foreign key constraints before executing the DROP or ALTER TABLE 854 disable foreign key constraints before executing the DROP or ALTER TABLE 851 command. Of course, while foreign key constraints are disabled, there is not 855 command. Of course, while foreign key constraints are disabled, there is not 852 to stop the user from violating foreign key constraints and thus creating 856 to stop the user from violating foreign key constraints and thus creating 853 an internally inconsistent database. 857 an internally inconsistent database. 854 858 855 859 856 860 857 [h1 "Limits and Unsupported Features" fk_unsupported] | 861 <h1 id=fk_unsupported>Limits and Unsupported Features</h1> 858 862 859 <p> 863 <p> 860 This section lists a few limitations and omitted features that are not 864 This section lists a few limitations and omitted features that are not 861 mentioned elsewhere. 865 mentioned elsewhere. 862 866 863 <ol> 867 <ol> 864 <li><p> 868 <li><p> ................................................................................................................................................................................ 884 constraints between <a href=#fk_deferred>deferred</a> and immediate 888 constraints between <a href=#fk_deferred>deferred</a> and immediate 885 mode at runtime (for example using the Oracle "SET CONSTRAINT" command) 889 mode at runtime (for example using the Oracle "SET CONSTRAINT" command) 886 SQLite does not support this. ^In SQLite, a foreign key constraint is 890 SQLite does not support this. ^In SQLite, a foreign key constraint is 887 permanently marked as deferred or immediate when it is created. 891 permanently marked as deferred or immediate when it is created. 888 892 889 <li><p> 893 <li><p> 890 <b>Recursion limit on foreign key actions.</b> ^(The 894 <b>Recursion limit on foreign key actions.</b> ^(The 891 \[SQLITE_MAX_TRIGGER_DEPTH\] and \[SQLITE_LIMIT_TRIGGER_DEPTH\] | 895 [SQLITE_MAX_TRIGGER_DEPTH] and [SQLITE_LIMIT_TRIGGER_DEPTH] 892 settings determine the maximum allowable depth of trigger 896 settings determine the maximum allowable depth of trigger 893 program recursion. For the purposes of these limits, 897 program recursion. For the purposes of these limits, 894 \[foreign key actions\] are considered trigger programs.)^ ^The | 898 [foreign key actions] are considered trigger programs.)^ ^The 895 \[PRAGMA recursive_triggers\] setting does not not affect the operation | 899 [PRAGMA recursive_triggers] setting does not not affect the operation 896 of foreign key actions. It is not possible to disable recursive foreign 900 of foreign key actions. It is not possible to disable recursive foreign 897 key actions. 901 key actions. 898 </ol> 902 </ol> 899 } <