OLD | NEW |
(Empty) | |
| 1 |
| 2 OVERVIEW |
| 3 |
| 4 The SQLite library is capable of parsing SQL foreign key constraints |
| 5 supplied as part of CREATE TABLE statements, but it does not actually |
| 6 implement them. However, most of the features of foreign keys may be |
| 7 implemented using SQL triggers, which SQLite does support. This text |
| 8 file describes a feature of the SQLite shell tool (sqlite3) that |
| 9 extracts foreign key definitions from an existing SQLite database and |
| 10 creates the set of CREATE TRIGGER statements required to implement |
| 11 the foreign key constraints. |
| 12 |
| 13 CAPABILITIES |
| 14 |
| 15 An SQL foreign key is a constraint that requires that each row in |
| 16 the "child" table corresponds to a row in the "parent" table. For |
| 17 example, the following schema: |
| 18 |
| 19 CREATE TABLE parent(a, b, c, PRIMARY KEY(a, b)); |
| 20 CREATE TABLE child(d, e, f, FOREIGN KEY(d, e) REFERENCES parent(a, b)); |
| 21 |
| 22 implies that for each row in table "child", there must be a row in |
| 23 "parent" for which the expression (child.d==parent.a AND child.e==parent.b) |
| 24 is true. The columns in the parent table are required to be either the |
| 25 primary key columns or subject to a UNIQUE constraint. There is no such |
| 26 requirement for the columns of the child table. |
| 27 |
| 28 At this time, all foreign keys are implemented as if they were |
| 29 "MATCH NONE", even if the declaration specified "MATCH PARTIAL" or |
| 30 "MATCH FULL". "MATCH NONE" means that if any of the key columns in |
| 31 the child table are NULL, then there is no requirement for a corresponding |
| 32 row in the parent table. So, taking this into account, the expression that |
| 33 must be true for every row of the child table in the above example is |
| 34 actually: |
| 35 |
| 36 (child.d IS NULL) OR |
| 37 (child.e IS NULL) OR |
| 38 (child.d==parent.a AND child.e==parent.b) |
| 39 |
| 40 Attempting to insert or update a row in the child table so that the |
| 41 affected row violates this constraint results in an exception being |
| 42 thrown. |
| 43 |
| 44 The effect of attempting to delete or update a row in the parent table |
| 45 so that the constraint becomes untrue for one or more rows in the child |
| 46 table depends on the "ON DELETE" or "ON UPDATE" actions specified as |
| 47 part of the foreign key definition, respectively. Three different actions |
| 48 are supported: "RESTRICT" (the default), "CASCADE" and "SET NULL". SQLite |
| 49 will also parse the "SET DEFAULT" action, but this is not implemented |
| 50 and "RESTRICT" is used instead. |
| 51 |
| 52 RESTRICT: Attempting to update or delete a row in the parent table so |
| 53 that the constraint becomes untrue for one or more rows in |
| 54 the child table is not allowed. An exception is thrown. |
| 55 |
| 56 CASCADE: Instead of throwing an exception, all corresponding child table |
| 57 rows are either deleted (if the parent row is being deleted) |
| 58 or updated to match the new parent key values (if the parent |
| 59 row is being updated). |
| 60 |
| 61 SET NULL: Instead of throwing an exception, the foreign key fields of |
| 62 all corresponding child table rows are set to NULL. |
| 63 |
| 64 LIMITATIONS |
| 65 |
| 66 Apart from those limitiations described above: |
| 67 |
| 68 * Implicit mapping to composite primary keys is not supported. If |
| 69 a parent table has a composite primary key, then any child table |
| 70 that refers to it must explicitly map each column. For example, given |
| 71 the following definition of table "parent": |
| 72 |
| 73 CREATE TABLE parent(a, b, c, PRIMARY KEY(a, b)); |
| 74 |
| 75 only the first of the following two definitions of table "child" |
| 76 is supported: |
| 77 |
| 78 CREATE TABLE child(d, e, f, FOREIGN KEY(d, e) REFERENCES parent(a, b)); |
| 79 CREATE TABLE child(d, e, f, FOREIGN KEY(d, e) REFERENCES parent); |
| 80 |
| 81 An implicit reference to a composite primary key is detected as an |
| 82 error when the program is run (see below). |
| 83 |
| 84 * SQLite does not support recursive triggers, and therefore this program |
| 85 does not support recursive CASCADE or SET NULL foreign key |
| 86 relationships. If the parent and the child tables of a CASCADE or |
| 87 SET NULL foreign key are the same table, the generated triggers will |
| 88 malfunction. This is also true if the recursive foreign key constraint |
| 89 is indirect (for example if table A references table B which references |
| 90 table A with a CASCADE or SET NULL foreign key constraint). |
| 91 |
| 92 Recursive CASCADE or SET NULL foreign key relationships are *not* |
| 93 detected as errors when the program is run. Buyer beware. |
| 94 |
| 95 USAGE |
| 96 |
| 97 The functionality is accessed through an sqlite3 shell tool "dot-command": |
| 98 |
| 99 .genfkey ?--no-drop? ?--ignore-errors? ?--exec? |
| 100 |
| 101 When this command is run, it first checks the schema of the open SQLite |
| 102 database for foreign key related errors or inconsistencies. For example, |
| 103 a foreign key that refers to a parent table that does not exist, or |
| 104 a foreign key that refers to columns in a parent table that are not |
| 105 guaranteed to be unique. If such errors are found and the --ignore-errors |
| 106 option was not present, a message for each one is printed to stderr and |
| 107 no further processing takes place. |
| 108 |
| 109 If errors are found and the --ignore-errors option is passed, then |
| 110 no error messages are printed. No "CREATE TRIGGER" statements are generated |
| 111 for foriegn-key definitions that contained errors, they are silently |
| 112 ignored by subsequent processing. |
| 113 |
| 114 All triggers generated by this command have names that match the pattern |
| 115 "genfkey*". Unless the --no-drop option is specified, then the program |
| 116 also generates a "DROP TRIGGER" statement for each trigger that exists |
| 117 in the database with a name that matches this pattern. This allows the |
| 118 program to be used to upgrade a database schema for which foreign key |
| 119 triggers have already been installed (i.e. after new tables are created |
| 120 or existing tables dropped). |
| 121 |
| 122 Finally, a series of SQL trigger definitions (CREATE TRIGGER statements) |
| 123 that implement the foreign key constraints found in the database schema are |
| 124 generated. |
| 125 |
| 126 If the --exec option was passed, then all generated SQL is immediately |
| 127 executed on the database. Otherwise, the generated SQL strings are output |
| 128 in the same way as the results of SELECT queries are. Normally, this means |
| 129 they will be printed to stdout, but this can be configured using other |
| 130 dot-commands (i.e. ".output"). |
| 131 |
| 132 The simplest way to activate the foriegn key definitions in a database |
| 133 is simply to open it using the shell tool and enter the command |
| 134 ".genfkey --exec": |
| 135 |
| 136 sqlite> .genfkey --exec |
| 137 |
OLD | NEW |