tool/genfkey.README

   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