/ Check-in [5ec07fee]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add tests to e_fkey.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 5ec07feea47e727824b8b48236b5cce80539a455
User & Date: dan 2009-10-10 15:49:49
Context
2009-10-12
08:41
Add further test cases to e_fkey.test. check-in: d61cc0e1 user: dan tags: trunk
2009-10-10
15:49
Add tests to e_fkey.test. check-in: 5ec07fee user: dan tags: trunk
14:29
Update requirement text for sqlite3_open_blob(). Update evidence comment in e_fkey.test to conform to the latest requirement phrasing. check-in: 89c548ac user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to test/e_fkey.test.

    14     14   #
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   ifcapable {!foreignkey} { finish_test ; return }
    19     19   
    20     20   execsql "PRAGMA foreign_keys = ON"
           21  +
           22  +###########################################################################
           23  +### SECTION 1: Introduction to Foreign Key Constraints
           24  +###########################################################################
           25  +
           26  +###########################################################################
           27  +### SECTION 2: Enabling Foreign Key Support
           28  +###########################################################################
           29  +
           30  +###########################################################################
           31  +### SECTION 3: Required and Suggested Database Indexes
           32  +###########################################################################
           33  +
           34  +###########################################################################
           35  +### SECTION 4.1: Composite Foreign Key Constraints
           36  +###########################################################################
           37  +
           38  +###########################################################################
           39  +### SECTION 4.2: Deferred Foreign Key Constraints
           40  +###########################################################################
           41  +
           42  +#-------------------------------------------------------------------------
           43  +# Note: R-35290-16460 is tested below.
           44  +# TODO: R-30323-21917
           45  +
           46  +#-------------------------------------------------------------------------
           47  +# /* EV: R-09323-30470 */
           48  +#
           49  +# Test that if a statement violates an immediate FK constraint, and the
           50  +# database does not satisfy the FK constraint once all effects of the
           51  +# statement have been applied, an error is reported and the effects of
           52  +# the statement rolled back.
           53  +#
           54  +drop_all_tables
           55  +do_test e_fkey-33.1 {
           56  +  execsql {
           57  +    CREATE TABLE king(a, b, PRIMARY KEY(a));
           58  +    CREATE TABLE prince(c REFERENCES king, d);
           59  +  }
           60  +} {}
           61  +
           62  +do_test e_fkey-33.2 {
           63  +  # Execute a statement that violates the immediate FK constraint.
           64  +  catchsql { INSERT INTO prince VALUES(1, 2) }
           65  +} {1 {foreign key constraint failed}}
           66  +
           67  +do_test e_fkey-33.3 {
           68  +  # This time, use a trigger to fix the constraint violation before the
           69  +  # statement has finished executing. Then execute the same statement as
           70  +  # in the previous test case. This time, no error.
           71  +  execsql {
           72  +    CREATE TRIGGER kt AFTER INSERT ON prince WHEN
           73  +      NOT EXISTS (SELECT a FROM king WHERE a = new.c)
           74  +    BEGIN
           75  +      INSERT INTO king VALUES(new.c, NULL);
           76  +    END
           77  +  }
           78  +  execsql { INSERT INTO prince VALUES(1, 2) }
           79  +} {}
           80  +
           81  +# Test that operating inside a transaction makes no difference to 
           82  +# immediate constraint violation handling.
           83  +do_test e_fkey-33.4 {
           84  +  execsql {
           85  +    BEGIN;
           86  +    INSERT INTO prince VALUES(2, 3);
           87  +    DROP TRIGGER kt;
           88  +  }
           89  +  catchsql { INSERT INTO prince VALUES(3, 4) }
           90  +} {1 {foreign key constraint failed}}
           91  +do_test e_fkey-33.5 {
           92  +  execsql {
           93  +    COMMIT;
           94  +    SELECT * FROM king;
           95  +  }
           96  +} {1 {} 2 {}}
           97  +
           98  +#-------------------------------------------------------------------------
           99  +# /* EV: R-49178-21358 */
          100  +# /* EV: R-39692-12488 */
          101  +# /* EV: R-55147-47664 */
          102  +# /* EV: R-29604-30395 */
          103  +#
          104  +# Test that if a deferred constraint is violated within a transaction,
          105  +# nothing happens immediately and the database is allowed to persist
          106  +# in a state that does not satisfy the FK constraint. However attempts
          107  +# to COMMIT the transaction fail until the FK constraint is satisfied.
          108  +#
          109  +proc test_efkey_34 {tn isError sql} {
          110  +  do_test e_fkey-34.$tn "
          111  +    catchsql {$sql}
          112  +  " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError]
          113  +}
          114  +drop_all_tables
          115  +
          116  +test_efkey_34  1 0 {
          117  +  CREATE TABLE ll(k PRIMARY KEY);
          118  +  CREATE TABLE kk(c REFERENCES ll DEFERRABLE INITIALLY DEFERRED);
          119  +}
          120  +test_efkey_34  2 0 "BEGIN"
          121  +test_efkey_34  3 0   "INSERT INTO kk VALUES(5)"
          122  +test_efkey_34  4 0   "INSERT INTO kk VALUES(10)"
          123  +test_efkey_34  5 1 "COMMIT"
          124  +test_efkey_34  6 0   "INSERT INTO ll VALUES(10)"
          125  +test_efkey_34  7 1 "COMMIT"
          126  +test_efkey_34  8 0   "INSERT INTO ll VALUES(5)"
          127  +test_efkey_34  9 0 "COMMIT"
          128  +
          129  +#-------------------------------------------------------------------------
          130  +# /* EV: R-56844-61705 */
          131  +#
          132  +# When not running inside a transaction, a deferred constraint is similar
          133  +# to an immediate constraint (violations are reported immediately).
          134  +#
          135  +drop_all_tables
          136  +proc test_efkey_35 {tn isError sql} {
          137  +  do_test e_fkey-35.$tn "
          138  +    catchsql {$sql}
          139  +  " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError]
          140  +}
          141  +do_test e_fkey-35.1 {
          142  +  execsql {
          143  +    CREATE TABLE parent(x, y);
          144  +    CREATE UNIQUE INDEX pi ON parent(x, y);
          145  +    CREATE TABLE child(a, b,
          146  +      FOREIGN KEY(a, b) REFERENCES parent(x, y) DEFERRABLE INITIALLY DEFERRED
          147  +    );
          148  +  }
          149  +} {}
          150  +test_efkey_35 2 1 "INSERT INTO child  VALUES('x', 'y')"
          151  +test_efkey_35 3 0 "INSERT INTO parent VALUES('x', 'y')"
          152  +test_efkey_35 4 0 "INSERT INTO child  VALUES('x', 'y')"
          153  +
          154  +
          155  +#-------------------------------------------------------------------------
          156  +# /* EV: R-12782-61841 */
          157  +#
          158  +# Test that an FK constraint is made deferred by adding the following
          159  +# to the definition:
          160  +#
          161  +#   DEFERRABLE INITIALLY DEFERRED
          162  +#
          163  +# /* EV: R-54882-46975 */
          164  +#
          165  +# Also test that adding any of the following to a foreign key definition 
          166  +# makes the constraint IMMEDIATE:
          167  +#
          168  +#   NOT DEFERRABLE INITIALLY DEFERRED
          169  +#   DEFERRABLE INITIALLY IMMEDIATE
          170  +#   DEFERRABLE
          171  +#
          172  +# /* EV: R-35290-16460 */
          173  +#
          174  +# Foreign keys are IMMEDIATE by default (if there is no DEFERRABLE or NOT
          175  +# DEFERRABLE clause).
          176  +#
          177  +drop_all_tables
          178  +do_test e_fkey-29.1 {
          179  +  execsql {
          180  +    CREATE TABLE parent(x, y, z, PRIMARY KEY(x,y,z));
          181  +    CREATE TABLE c1(a, b, c,
          182  +      FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY DEFERRED
          183  +    );
          184  +    CREATE TABLE c2(a, b, c,
          185  +      FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY IMMEDIATE
          186  +    );
          187  +    CREATE TABLE c3(a, b, c,
          188  +      FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE
          189  +    );
          190  +    CREATE TABLE c4(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent);
          191  +
          192  +    -- This FK constraint is the only deferrable one.
          193  +    CREATE TABLE c5(a, b, c,
          194  +      FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY DEFERRED
          195  +    );
          196  +
          197  +    INSERT INTO parent VALUES('a', 'b', 'c');
          198  +    INSERT INTO parent VALUES('d', 'e', 'f');
          199  +    INSERT INTO parent VALUES('g', 'h', 'i');
          200  +    INSERT INTO parent VALUES('j', 'k', 'l');
          201  +    INSERT INTO parent VALUES('m', 'n', 'o');
          202  +
          203  +    INSERT INTO c1 VALUES('a', 'b', 'c');
          204  +    INSERT INTO c2 VALUES('d', 'e', 'f');
          205  +    INSERT INTO c3 VALUES('g', 'h', 'i');
          206  +    INSERT INTO c4 VALUES('j', 'k', 'l');
          207  +    INSERT INTO c5 VALUES('m', 'n', 'o');
          208  +  }
          209  +} {}
          210  +
          211  +proc test_efkey_29 {tn sql isError} {
          212  +  do_test e_fkey-29.$tn "catchsql {$sql}" [
          213  +    lindex {{0 {}} {1 {foreign key constraint failed}}} $isError
          214  +  ]
          215  +}
          216  +test_efkey_29  2 "BEGIN"                                   0
          217  +test_efkey_29  3 "DELETE FROM parent WHERE x = 'a'"        1
          218  +test_efkey_29  4 "DELETE FROM parent WHERE x = 'd'"        1
          219  +test_efkey_29  5 "DELETE FROM parent WHERE x = 'g'"        1
          220  +test_efkey_29  6 "DELETE FROM parent WHERE x = 'j'"        1
          221  +test_efkey_29  7 "DELETE FROM parent WHERE x = 'm'"        0
          222  +test_efkey_29  8 "COMMIT"                                  1
          223  +test_efkey_29  9 "ROLLBACK"                                0
          224  +
          225  +test_efkey_29  9 "BEGIN"                                   0
          226  +test_efkey_29 10 "UPDATE parent SET z = 'z' WHERE z = 'c'" 1
          227  +test_efkey_29 11 "UPDATE parent SET z = 'z' WHERE z = 'f'" 1
          228  +test_efkey_29 12 "UPDATE parent SET z = 'z' WHERE z = 'i'" 1
          229  +test_efkey_29 13 "UPDATE parent SET z = 'z' WHERE z = 'l'" 1
          230  +test_efkey_29 14 "UPDATE parent SET z = 'z' WHERE z = 'o'" 0
          231  +test_efkey_29 15 "COMMIT"                                  1
          232  +test_efkey_29 16 "ROLLBACK"                                0
          233  +
          234  +test_efkey_29 17 "BEGIN"                                   0
          235  +test_efkey_29 18 "INSERT INTO c1 VALUES(1, 2, 3)"          1
          236  +test_efkey_29 19 "INSERT INTO c2 VALUES(1, 2, 3)"          1
          237  +test_efkey_29 20 "INSERT INTO c3 VALUES(1, 2, 3)"          1
          238  +test_efkey_29 21 "INSERT INTO c4 VALUES(1, 2, 3)"          1
          239  +test_efkey_29 22 "INSERT INTO c5 VALUES(1, 2, 3)"          0
          240  +test_efkey_29 23 "COMMIT"                                  1
          241  +test_efkey_29 24 "INSERT INTO parent VALUES(1, 2, 3)"      0
          242  +test_efkey_29 25 "COMMIT"                                  0
          243  +
          244  +test_efkey_29 26 "BEGIN"                                   0
          245  +test_efkey_29 27 "UPDATE c1 SET a = 10"                    1
          246  +test_efkey_29 28 "UPDATE c2 SET a = 10"                    1
          247  +test_efkey_29 29 "UPDATE c3 SET a = 10"                    1
          248  +test_efkey_29 30 "UPDATE c4 SET a = 10"                    1
          249  +test_efkey_29 31 "UPDATE c5 SET a = 10"                    0
          250  +test_efkey_29 32 "COMMIT"                                  1
          251  +test_efkey_29 33 "ROLLBACK"                                0
          252  +
          253  +#-------------------------------------------------------------------------
          254  +# /* EV: R-27340-26081 */
          255  +#
          256  +# Test an example from foreignkeys.html dealing with a deferred foreign 
          257  +# key constraint.
          258  +#
          259  +do_test e_fkey-28.1 {
          260  +  drop_all_tables
          261  +  execsql {
          262  +    CREATE TABLE artist(
          263  +      artistid    INTEGER PRIMARY KEY, 
          264  +      artistname  TEXT
          265  +    );
          266  +    CREATE TABLE track(
          267  +      trackid     INTEGER,
          268  +      trackname   TEXT, 
          269  +      trackartist INTEGER REFERENCES artist(artistid) DEFERRABLE INITIALLY DEFERRED
          270  +    );
          271  +  }
          272  +} {}
          273  +do_test e_fkey-28.2 {
          274  +  execsql {
          275  +    BEGIN;
          276  +      INSERT INTO track VALUES(1, 'White Christmas', 5);
          277  +  }
          278  +  catchsql COMMIT
          279  +} {1 {foreign key constraint failed}}
          280  +do_test e_fkey-28.3 {
          281  +  execsql {
          282  +    INSERT INTO artist VALUES(5, 'Bing Crosby');
          283  +    COMMIT;
          284  +  }
          285  +} {}
          286  +
          287  +#-------------------------------------------------------------------------
          288  +# /* EV: R-07223-48323 */
          289  +#
          290  +# Verify that a nested savepoint may be released without satisfying 
          291  +# deferred foreign key constraints.
          292  +#
          293  +drop_all_tables
          294  +do_test e_fkey-30.1 {
          295  +  execsql {
          296  +    CREATE TABLE t1(a PRIMARY KEY,
          297  +      b REFERENCES t1 DEFERRABLE INITIALLY DEFERRED
          298  +    );
          299  +    INSERT INTO t1 VALUES(1, 1);
          300  +    INSERT INTO t1 VALUES(2, 2);
          301  +    INSERT INTO t1 VALUES(3, 3);
          302  +  }
          303  +} {}
          304  +do_test e_fkey-30.2 {
          305  +  execsql {
          306  +    BEGIN;
          307  +      SAVEPOINT one;
          308  +        INSERT INTO t1 VALUES(4, 5);
          309  +      RELEASE one;
          310  +  }
          311  +} {}
          312  +do_test e_fkey-30.3 {
          313  +  catchsql COMMIT
          314  +} {1 {foreign key constraint failed}}
          315  +do_test e_fkey-30.4 {
          316  +  execsql {
          317  +    UPDATE t1 SET a = 5 WHERE a = 4;
          318  +    COMMIT;
          319  +  }
          320  +} {}
          321  +
          322  +
          323  +#-------------------------------------------------------------------------
          324  +# /* EV: R-44295-13823 */
          325  +#
          326  +# Check that a transaction savepoint (an outermost savepoint opened when
          327  +# the database was in auto-commit mode) cannot be released without
          328  +# satisfying deferred foreign key constraints. It may be rolled back.
          329  +#
          330  +do_test e_fkey-31.1 {
          331  +  execsql {
          332  +    SAVEPOINT one;
          333  +      SAVEPOINT two;
          334  +        INSERT INTO t1 VALUES(6, 7);
          335  +      RELEASE two;
          336  +  }
          337  +} {}
          338  +do_test e_fkey-31.2 {
          339  +  catchsql {RELEASE one}
          340  +} {1 {foreign key constraint failed}}
          341  +do_test e_fkey-31.3 {
          342  +  execsql {
          343  +      UPDATE t1 SET a = 7 WHERE a = 6;
          344  +    RELEASE one;
          345  +  }
          346  +} {}
          347  +do_test e_fkey-31.4 {
          348  +  execsql {
          349  +    SAVEPOINT one;
          350  +      SAVEPOINT two;
          351  +        INSERT INTO t1 VALUES(9, 10);
          352  +      RELEASE two;
          353  +  }
          354  +} {}
          355  +do_test e_fkey-31.5 {
          356  +  catchsql {RELEASE one}
          357  +} {1 {foreign key constraint failed}}
          358  +do_test e_fkey-31.6 {
          359  +  execsql {ROLLBACK TO one ; RELEASE one}
          360  +} {}
          361  +
          362  +#-------------------------------------------------------------------------
          363  +# /* EV: R-37736-42616 */
          364  +#
          365  +# Test that if a COMMIT operation fails due to deferred foreign key 
          366  +# constraints, any nested savepoints remain open.
          367  +#
          368  +do_test e_fkey-32.1 {
          369  +  execsql {
          370  +    DELETE FROM t1 WHERE a>3;
          371  +    SELECT * FROM t1;
          372  +  }
          373  +} {1 1 2 2 3 3}
          374  +do_test e_fkey-32.2 {
          375  +  execsql {
          376  +    BEGIN;
          377  +      INSERT INTO t1 VALUES(4, 4);
          378  +      SAVEPOINT one;
          379  +        INSERT INTO t1 VALUES(5, 6);
          380  +        SELECT * FROM t1;
          381  +  }
          382  +} {1 1 2 2 3 3 4 4 5 6}
          383  +do_test e_fkey-32.3 {
          384  +  catchsql COMMIT
          385  +} {1 {foreign key constraint failed}}
          386  +do_test e_fkey-32.4 {
          387  +  execsql {
          388  +    ROLLBACK TO one;
          389  +    COMMIT;
          390  +    SELECT * FROM t1;
          391  +  }
          392  +} {1 1 2 2 3 3 4 4}
          393  +
          394  +do_test e_fkey-32.5 {
          395  +  execsql {
          396  +    SAVEPOINT a;
          397  +      INSERT INTO t1 VALUES(5, 5);
          398  +      SAVEPOINT b;
          399  +        INSERT INTO t1 VALUES(6, 7);
          400  +        SAVEPOINT c;
          401  +          INSERT INTO t1 VALUES(7, 8);
          402  +  }
          403  +} {}
          404  +do_test e_fkey-32.6 {
          405  +  catchsql {RELEASE a}
          406  +} {1 {foreign key constraint failed}}
          407  +do_test e_fkey-32.7 {
          408  +  execsql  {ROLLBACK TO c}
          409  +  catchsql {RELEASE a}
          410  +} {1 {foreign key constraint failed}}
          411  +do_test e_fkey-32.8 {
          412  +  execsql  {
          413  +    ROLLBACK TO b;
          414  +    RELEASE a;
          415  +    SELECT * FROM t1;
          416  +  }
          417  +} {1 1 2 2 3 3 4 4 5 5}
    21    418   
    22    419   ###########################################################################
    23    420   ### SECTION 4.3: ON DELETE and ON UPDATE Actions
    24    421   ###########################################################################
    25    422   
    26    423   #-------------------------------------------------------------------------
    27    424   # /* EV: R-48270-44282 */
................................................................................
   774   1171   # Test that parent keys are not checked when tables are created.
   775   1172   #
   776   1173   # Child keys are checked to ensure all component columns exist. If parent
   777   1174   # key columns are explicitly specified, SQLite checks to make sure there
   778   1175   # are the same number of columns in the child and parent keys. (TODO: This
   779   1176   # is tested but does not correspond to any testable statement.)
   780   1177   #
   781         -# EV: R-08908-23439 A CREATE TABLE command operates the same whether 
   782         -# or not foreign key constraints are enabled. 
         1178  +# /* EV: R-08908-23439 */
   783   1179   #
   784   1180   # Also test that the above statements are true regardless of whether or not
   785         -# foreign keys are enabled.
         1181  +# foreign keys are enabled:  "A CREATE TABLE command operates the same whether
         1182  +# or not foreign key constraints are enabled."
   786   1183   # 
   787   1184   foreach {tn zCreateTbl lRes} {
   788   1185     1 "CREATE TABLE t1(a, b REFERENCES t1)"                            {0 {}}
   789   1186     2 "CREATE TABLE t1(a, b REFERENCES t2)"                            {0 {}}
   790   1187     3 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1)"          {0 {}}
   791   1188     4 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)"          {0 {}}
   792   1189     5 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)"          {0 {}}