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: |
7889bfe143a5d53339e94ed131caf2ef |
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
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 | <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> SELECT * FROM artist; artistid artistname -------- ----------------- 1 Dean Martin 2 Frank Sinatra sqlite> SELECT * FROM track; |
︙ | ︙ | |||
135 136 137 138 139 140 141 | sqlite> INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; sqlite> <i>-- Now that "Sammy Davis Jr." (artistid = 3) has been added to the database,</i> sqlite> <i>-- it is possible to INSERT new tracks using this artist without violating</i> sqlite> <i>-- the foreign key constraint:</i> sqlite> INSERT INTO track VALUES(15, 'Boogie Woogie', 3); | | | | > | > | 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> INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; sqlite> <i>-- Now that "Sammy Davis Jr." (artistid = 3) has been added to the database,</i> sqlite> <i>-- it is possible to INSERT new tracks using this artist without violating</i> sqlite> <i>-- the foreign key constraint:</i> sqlite> 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> <i>-- Attempting to delete the artist record for "Frank Sinatra" fails, since</i> sqlite> <i>-- the track table contains a row that refer to it.</i> sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra'; <b>SQL error: foreign key constraint failed</b> sqlite> <i>-- Delete all the records from the track table that refer to the artist</i> sqlite> <i>-- "Frank Sinatra". Only then is it possible to delete the artist.</i> sqlite> DELETE FROM track WHERE trackname = 'My Way'; sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra'; sqlite> <i>-- Try to update the artistid of a row in the artist table while there</i> sqlite> <i>-- exists records in the track table that refer to it. </i> sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; <b>SQL error: foreign key constraint failed</b> sqlite> <i>-- Once all the records that refer to a row in the artist table have</i> sqlite> <i>-- been deleted, it is possible to modify the artistid of the row.</i> sqlite> DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues'); sqlite> 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 | <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> | | | > | > | | | | | | < > | | | | | | | | | | < > | 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> 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 | 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: | | | | | < > | | | | | | | | < > | | | | | | | | | | | 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 <child-table> WHERE <child-key> = :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><parent-table></i>" clause to a column definition creates a foreign key constraint that maps the column to the primary key of <i><parent-table></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 | <b>SQL error: foreign key constraint failed</b> sqlite3> <i>-- After inserting a row into the artist table with artistid=5, the</i> sqlite3> <i>-- deferred foreign key constraint is satisfied. It is then possible</i> sqlite3> <i>-- to commit the transaction without error.</i> sqlite3> INSERT INTO artist VALUES(5, 'Bing Crosby'); sqlite3> COMMIT; | | | | > | > | 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> <i>-- After inserting a row into the artist table with artistid=5, the</i> sqlite3> <i>-- deferred foreign key constraint is satisfied. It is then possible</i> sqlite3> <i>-- to commit the transaction without error.</i> sqlite3> INSERT INTO artist VALUES(5, 'Bing Crosby'); sqlite3> 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 | 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 | | | | 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 | sqlite> SELECT * FROM track; trackid trackname trackartist ------- ----------------- ----------- 11 That's Amore 100 12 Christmas Blues 100 13 My Way 2 | | | | 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> 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 | -------- ----------------- 0 Unknown Artist sqlite> SELECT * FROM track; trackid trackname trackartist ------- ----------------- ----------- 14 Mr. Bojangles 0 | | | | < > | | 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> 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> SELECT * FROM parent; x ---- |
︙ | ︙ | |||
773 774 775 776 777 778 779 | sqlite> <i>-- value, the ON UPDATE action is performed and the child key is set</i> sqlite> <i>-- to NULL.</i> sqlite> UPDATE parent SET x = 'key2'; sqlite> SELECT IFNULL(y, 'null') FROM child; y ---- null | | | | | | | | | | | | | | | 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> <i>-- value, the ON UPDATE action is performed and the child key is set</i> sqlite> <i>-- to NULL.</i> sqlite> UPDATE parent SET x = 'key2'; sqlite> 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 | 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 | | | | < | 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> |