Return to genfkey.README CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / tool

Annotation of embedaddon/sqlite3/tool/genfkey.README, revision 1.1.1.1

1.1       misho       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: