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     ! 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: