/ Check-in [e391dfe7]
Login

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

Overview
Comment:Add test cases to e_fkey.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e391dfe79d52e1de48688715d4a6707ffca214fd
User & Date: dan 2009-10-09 18:16:55
Context
2009-10-10
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
2009-10-09
18:16
Add test cases to e_fkey.test. check-in: e391dfe7 user: dan tags: trunk
2009-10-08
19:02
Test a couple of the examples in foreignkeys.html. check-in: a2fb7902 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_fkey.test.

    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     21   
    22         -#-------------------------------------------------------------------------
    23         -# /* EV: R-51437-39891 */
    24         -#
    25         -# Test an example from the "ON DELETE and ON UPDATE Actions" section 
    26         -# of foreignkeys.html.
    27         -#
    28         -drop_all_tables
    29         -do_test e_fkey-13.1 {
           22  +###########################################################################
           23  +### SECTION 4.3: ON DELETE and ON UPDATE Actions
           24  +###########################################################################
           25  +
           26  +#-------------------------------------------------------------------------
           27  +# /* EV: R-48270-44282 */
           28  +#
           29  +# Test that configured ON DELETE and ON UPDATE actions take place when
           30  +# deleting or modifying rows of the parent table, respectively.
           31  +#
           32  +# /* EV: R-48124-63225 */
           33  +#
           34  +# Test that a single FK constraint may have different actions configured
           35  +# for ON DELETE and ON UPDATE.
           36  +#
           37  +do_test e_fkey-16.1 {
           38  +  execsql {
           39  +    CREATE TABLE p(a, b PRIMARY KEY, c);
           40  +    CREATE TABLE c1(d, e, f DEFAULT 'k0' REFERENCES p 
           41  +      ON UPDATE SET DEFAULT
           42  +      ON DELETE SET NULL
           43  +    );
           44  +
           45  +    INSERT INTO p VALUES(0, 'k0', '');
           46  +    INSERT INTO p VALUES(1, 'k1', 'I');
           47  +    INSERT INTO p VALUES(2, 'k2', 'II');
           48  +    INSERT INTO p VALUES(3, 'k3', 'III');
           49  +
           50  +    INSERT INTO c1 VALUES(1, 'xx', 'k1');
           51  +    INSERT INTO c1 VALUES(2, 'xx', 'k2');
           52  +    INSERT INTO c1 VALUES(3, 'xx', 'k3');
           53  +  }
           54  +} {}
           55  +do_test e_fkey-16.2 {
           56  +  execsql {
           57  +    UPDATE p SET b = 'k4' WHERE a = 1;
           58  +    SELECT * FROM c1;
           59  +  }
           60  +} {1 xx k0 2 xx k2 3 xx k3}
           61  +do_test e_fkey-16.3 {
           62  +  execsql {
           63  +    DELETE FROM p WHERE a = 2;
           64  +    SELECT * FROM c1;
           65  +  }
           66  +} {1 xx k0 2 xx {} 3 xx k3}
           67  +do_test e_fkey-16.4 {
           68  +  execsql {
           69  +    CREATE UNIQUE INDEX pi ON p(c);
           70  +    REPLACE INTO p VALUES(5, 'k5', 'III');
           71  +    SELECT * FROM c1;
           72  +  }
           73  +} {1 xx k0 2 xx {} 3 xx {}}
           74  +
           75  +#-------------------------------------------------------------------------
           76  +# /* EV: R-33326-45252 */
           77  +#
           78  +# Each foreign key in the system has an ON UPDATE and ON DELETE action,
           79  +# either "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".
           80  +#
           81  +# /* EV: R-19803-45884 */
           82  +#
           83  +# If none is specified explicitly, "NO ACTION" is the default.
           84  +# 
           85  +drop_all_tables
           86  +do_test e_fkey-17.1 {
           87  +  execsql {
           88  +    CREATE TABLE parent(x PRIMARY KEY, y);
           89  +    CREATE TABLE child1(a, 
           90  +      b REFERENCES parent ON UPDATE NO ACTION ON DELETE RESTRICT
           91  +    );
           92  +    CREATE TABLE child2(a, 
           93  +      b REFERENCES parent ON UPDATE RESTRICT ON DELETE SET NULL
           94  +    );
           95  +    CREATE TABLE child3(a, 
           96  +      b REFERENCES parent ON UPDATE SET NULL ON DELETE SET DEFAULT
           97  +    );
           98  +    CREATE TABLE child4(a, 
           99  +      b REFERENCES parent ON UPDATE SET DEFAULT ON DELETE CASCADE
          100  +    );
          101  +
          102  +    -- Create some foreign keys that use the default action - "NO ACTION"
          103  +    CREATE TABLE child5(a, b REFERENCES parent ON UPDATE CASCADE);
          104  +    CREATE TABLE child6(a, b REFERENCES parent ON DELETE RESTRICT);
          105  +    CREATE TABLE child7(a, b REFERENCES parent ON DELETE NO ACTION);
          106  +    CREATE TABLE child8(a, b REFERENCES parent ON UPDATE NO ACTION);
          107  +  }
          108  +} {}
          109  +
          110  +foreach {tn zTab lRes} {
          111  +  2 child1 {0 0 parent b {} {NO ACTION} RESTRICT NONE}
          112  +  3 child2 {0 0 parent b {} RESTRICT {SET NULL} NONE}
          113  +  4 child3 {0 0 parent b {} {SET NULL} {SET DEFAULT} NONE}
          114  +  5 child4 {0 0 parent b {} {SET DEFAULT} CASCADE NONE}
          115  +  6 child5 {0 0 parent b {} CASCADE {NO ACTION} NONE}
          116  +  7 child6 {0 0 parent b {} {NO ACTION} RESTRICT NONE}
          117  +  8 child7 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
          118  +  9 child8 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
          119  +} {
          120  +  do_test e_fkey-17.$tn { execsql "PRAGMA foreign_key_list($zTab)" } $lRes
          121  +}
          122  +
          123  +#-------------------------------------------------------------------------
          124  +# /* EV: R-19971-54976 */
          125  +#
          126  +# Test that "NO ACTION" means that nothing happens to a child row when
          127  +# it's parent row is updated or deleted.
          128  +#
          129  +drop_all_tables
          130  +do_test e_fkey-18.1 {
          131  +  execsql {
          132  +    CREATE TABLE parent(p1, p2, PRIMARY KEY(p1, p2));
          133  +    CREATE TABLE child(c1, c2, 
          134  +      FOREIGN KEY(c1, c2) REFERENCES parent
          135  +      ON UPDATE NO ACTION
          136  +      ON DELETE NO ACTION
          137  +      DEFERRABLE INITIALLY DEFERRED
          138  +    );
          139  +    INSERT INTO parent VALUES('j', 'k');
          140  +    INSERT INTO parent VALUES('l', 'm');
          141  +    INSERT INTO child VALUES('j', 'k');
          142  +    INSERT INTO child VALUES('l', 'm');
          143  +  }
          144  +} {}
          145  +do_test e_fkey-18.2 {
          146  +  execsql {
          147  +    BEGIN;
          148  +      UPDATE parent SET p1='k' WHERE p1='j';
          149  +      DELETE FROM parent WHERE p1='l';
          150  +      SELECT * FROM child;
          151  +  }
          152  +} {j k l m}
          153  +do_test e_fkey-18.3 {
          154  +  catchsql COMMIT
          155  +} {1 {foreign key constraint failed}}
          156  +do_test e_fkey-18.4 {
          157  +  execsql ROLLBACK
          158  +} {}
          159  +
          160  +#-------------------------------------------------------------------------
          161  +# /* EV: R-04272-38653 */
          162  +#
          163  +# Test that "RESTRICT" means the application is prohibited from deleting
          164  +# or updating a parent table row when there exists one or more child keys
          165  +# mapped to it.
          166  +#
          167  +drop_all_tables
          168  +do_test e_fkey-18.1 {
          169  +  execsql {
          170  +    CREATE TABLE parent(p1, p2);
          171  +    CREATE UNIQUE INDEX parent_i ON parent(p1, p2);
          172  +    CREATE TABLE child1(c1, c2, 
          173  +      FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON DELETE RESTRICT
          174  +    );
          175  +    CREATE TABLE child2(c1, c2, 
          176  +      FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON UPDATE RESTRICT
          177  +    );
          178  +  }
          179  +} {}
          180  +do_test e_fkey-18.2 {
          181  +  execsql {
          182  +    INSERT INTO parent VALUES('a', 'b');
          183  +    INSERT INTO parent VALUES('c', 'd');
          184  +    INSERT INTO child1 VALUES('b', 'a');
          185  +    INSERT INTO child2 VALUES('d', 'c');
          186  +  }
          187  +} {}
          188  +do_test e_fkey-18.3 {
          189  +  catchsql { DELETE FROM parent WHERE p1 = 'a' }
          190  +} {1 {foreign key constraint failed}}
          191  +do_test e_fkey-18.4 {
          192  +  catchsql { UPDATE parent SET p2 = 'e' WHERE p1 = 'c' }
          193  +} {1 {foreign key constraint failed}}
          194  +
          195  +#-------------------------------------------------------------------------
          196  +# /* EV: R-37997-42187 */
          197  +# 
          198  +# Test that RESTRICT is slightly different from NO ACTION for IMMEDIATE
          199  +# constraints, in that it is enforced immediately, not at the end of the 
          200  +# statement.
          201  +#
          202  +drop_all_tables
          203  +do_test e_fkey-19.1 {
          204  +  execsql {
          205  +    CREATE TABLE parent(x PRIMARY KEY);
          206  +    CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT);
          207  +    CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION);
          208  +
          209  +    INSERT INTO parent VALUES('key1');
          210  +    INSERT INTO parent VALUES('key2');
          211  +    INSERT INTO child1 VALUES('key1');
          212  +    INSERT INTO child2 VALUES('key2');
          213  +
          214  +    CREATE TRIGGER parent_t AFTER UPDATE ON parent BEGIN
          215  +      UPDATE child1 set c = new.x WHERE c = old.x;
          216  +      UPDATE child2 set c = new.x WHERE c = old.x;
          217  +    END;
          218  +  }
          219  +} {}
          220  +do_test e_fkey-19.2 {
          221  +  catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' }
          222  +} {1 {foreign key constraint failed}}
          223  +do_test e_fkey-19.3 {
          224  +  execsql { 
          225  +    UPDATE parent SET x = 'key two' WHERE x = 'key2';
          226  +    SELECT * FROM child2;
          227  +  }
          228  +} {{key two}}
          229  +
          230  +drop_all_tables
          231  +do_test e_fkey-19.4 {
          232  +  execsql {
          233  +    CREATE TABLE parent(x PRIMARY KEY);
          234  +    CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT);
          235  +    CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION);
          236  +
          237  +    INSERT INTO parent VALUES('key1');
          238  +    INSERT INTO parent VALUES('key2');
          239  +    INSERT INTO child1 VALUES('key1');
          240  +    INSERT INTO child2 VALUES('key2');
          241  +
          242  +    CREATE TRIGGER parent_t AFTER DELETE ON parent BEGIN
          243  +      UPDATE child1 SET c = NULL WHERE c = old.x;
          244  +      UPDATE child2 SET c = NULL WHERE c = old.x;
          245  +    END;
          246  +  }
          247  +} {}
          248  +do_test e_fkey-19.5 {
          249  +  catchsql { DELETE FROM parent WHERE x = 'key1' }
          250  +} {1 {foreign key constraint failed}}
          251  +do_test e_fkey-19.6 {
          252  +  execsql { 
          253  +    DELETE FROM parent WHERE x = 'key2';
          254  +    SELECT * FROM child2;
          255  +  }
          256  +} {{}}
          257  +
          258  +drop_all_tables
          259  +do_test e_fkey-19.7 {
          260  +  execsql {
          261  +    CREATE TABLE parent(x PRIMARY KEY);
          262  +    CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT);
          263  +    CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION);
          264  +
          265  +    INSERT INTO parent VALUES('key1');
          266  +    INSERT INTO parent VALUES('key2');
          267  +    INSERT INTO child1 VALUES('key1');
          268  +    INSERT INTO child2 VALUES('key2');
          269  +  }
          270  +} {}
          271  +do_test e_fkey-19.8 {
          272  +  catchsql { REPLACE INTO parent VALUES('key1') }
          273  +} {1 {foreign key constraint failed}}
          274  +do_test e_fkey-19.9 {
          275  +  execsql { 
          276  +    REPLACE INTO parent VALUES('key2');
          277  +    SELECT * FROM child2;
          278  +  }
          279  +} {key2}
          280  +
          281  +#-------------------------------------------------------------------------
          282  +# /* EV: R-24179-60523 */
          283  +# 
          284  +# Test that RESTRICT is enforced immediately, even for a DEFERRED constraint.
          285  +#
          286  +drop_all_tables
          287  +do_test e_fkey-20.1 {
          288  +  execsql {
          289  +    CREATE TABLE parent(x PRIMARY KEY);
          290  +    CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT
          291  +      DEFERRABLE INITIALLY DEFERRED
          292  +    );
          293  +    CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION
          294  +      DEFERRABLE INITIALLY DEFERRED
          295  +    );
          296  +
          297  +    INSERT INTO parent VALUES('key1');
          298  +    INSERT INTO parent VALUES('key2');
          299  +    INSERT INTO child1 VALUES('key1');
          300  +    INSERT INTO child2 VALUES('key2');
          301  +    BEGIN;
          302  +  }
          303  +} {}
          304  +do_test e_fkey-20.2 {
          305  +  catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' }
          306  +} {1 {foreign key constraint failed}}
          307  +do_test e_fkey-20.3 {
          308  +  execsql { UPDATE parent SET x = 'key two' WHERE x = 'key2' }
          309  +} {}
          310  +do_test e_fkey-20.4 {
          311  +  catchsql COMMIT
          312  +} {1 {foreign key constraint failed}}
          313  +do_test e_fkey-20.5 {
          314  +  execsql {
          315  +    UPDATE child2 SET c = 'key two';
          316  +    COMMIT;
          317  +  }
          318  +} {}
          319  +
          320  +drop_all_tables
          321  +do_test e_fkey-20.6 {
    30    322     execsql {
    31    323       CREATE TABLE parent(x PRIMARY KEY);
    32         -    CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL);
    33         -    INSERT INTO parent VALUES('key');
    34         -    INSERT INTO child VALUES('key');
    35         -  }
    36         -} {}
    37         -do_test e_fkey-13.2 {
    38         -  execsql {
    39         -    UPDATE parent SET x = 'key';
    40         -    SELECT IFNULL(y, 'null') FROM child;
    41         -  }
    42         -} {key}
    43         -do_test e_fkey-13.3 {
    44         -  execsql {
    45         -    UPDATE parent SET x = 'key2';
    46         -    SELECT IFNULL(y, 'null') FROM child;
    47         -  }
    48         -} {null}
    49         -
    50         -#-------------------------------------------------------------------------
    51         -# /* EV: R-07065-59588 */
    52         -#
    53         -# Test an example from the "ON DELETE and ON UPDATE Actions" section 
    54         -# of foreignkeys.html.
          324  +    CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT
          325  +      DEFERRABLE INITIALLY DEFERRED
          326  +    );
          327  +    CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION
          328  +      DEFERRABLE INITIALLY DEFERRED
          329  +    );
          330  +
          331  +    INSERT INTO parent VALUES('key1');
          332  +    INSERT INTO parent VALUES('key2');
          333  +    INSERT INTO child1 VALUES('key1');
          334  +    INSERT INTO child2 VALUES('key2');
          335  +    BEGIN;
          336  +  }
          337  +} {}
          338  +do_test e_fkey-20.7 {
          339  +  catchsql { DELETE FROM parent WHERE x = 'key1' }
          340  +} {1 {foreign key constraint failed}}
          341  +do_test e_fkey-20.8 {
          342  +  execsql { DELETE FROM parent WHERE x = 'key2' }
          343  +} {}
          344  +do_test e_fkey-20.9 {
          345  +  catchsql COMMIT
          346  +} {1 {foreign key constraint failed}}
          347  +do_test e_fkey-20.10 {
          348  +  execsql {
          349  +    UPDATE child2 SET c = NULL;
          350  +    COMMIT;
          351  +  }
          352  +} {}
          353  +
          354  +#-------------------------------------------------------------------------
          355  +# /* EV: R-03353-05327 */
          356  +#
          357  +# Test SET NULL actions.
          358  +#
          359  +drop_all_tables
          360  +do_test e_fkey-21.1 {
          361  +  execsql {
          362  +    CREATE TABLE pA(x PRIMARY KEY);
          363  +    CREATE TABLE cA(c REFERENCES pA ON DELETE SET NULL);
          364  +    CREATE TABLE cB(c REFERENCES pA ON UPDATE SET NULL);
          365  +
          366  +    INSERT INTO pA VALUES(X'ABCD');
          367  +    INSERT INTO pA VALUES(X'1234');
          368  +    INSERT INTO cA VALUES(X'ABCD');
          369  +    INSERT INTO cB VALUES(X'1234');
          370  +  }
          371  +} {}
          372  +do_test e_fkey-21.2 {
          373  +  execsql {
          374  +    DELETE FROM pA WHERE rowid = 1;
          375  +    SELECT quote(x) FROM pA;
          376  +  }
          377  +} {X'1234'}
          378  +do_test e_fkey-21.3 {
          379  +  execsql {
          380  +    SELECT quote(c) FROM cA;
          381  +  }
          382  +} {NULL}
          383  +do_test e_fkey-21.4 {
          384  +  execsql {
          385  +    UPDATE pA SET x = X'8765' WHERE rowid = 2;
          386  +    SELECT quote(x) FROM pA;
          387  +  }
          388  +} {X'8765'}
          389  +do_test e_fkey-21.5 {
          390  +  execsql { SELECT quote(c) FROM cB }
          391  +} {NULL}
          392  +
          393  +#-------------------------------------------------------------------------
          394  +# /* EV: R-43054-54832 */
          395  +#
          396  +# Test SET DEFAULT actions.
          397  +#
          398  +drop_all_tables
          399  +do_test e_fkey-22.1 {
          400  +  execsql {
          401  +    CREATE TABLE pA(x PRIMARY KEY);
          402  +    CREATE TABLE cA(c DEFAULT X'0000' REFERENCES pA ON DELETE SET DEFAULT);
          403  +    CREATE TABLE cB(c DEFAULT X'9999' REFERENCES pA ON UPDATE SET DEFAULT);
          404  +
          405  +    INSERT INTO pA(rowid, x) VALUES(1, X'0000');
          406  +    INSERT INTO pA(rowid, x) VALUES(2, X'9999');
          407  +    INSERT INTO pA(rowid, x) VALUES(3, X'ABCD');
          408  +    INSERT INTO pA(rowid, x) VALUES(4, X'1234');
          409  +
          410  +    INSERT INTO cA VALUES(X'ABCD');
          411  +    INSERT INTO cB VALUES(X'1234');
          412  +  }
          413  +} {}
          414  +do_test e_fkey-22.2 {
          415  +  execsql {
          416  +    DELETE FROM pA WHERE rowid = 3;
          417  +    SELECT quote(x) FROM pA;
          418  +  }
          419  +} {X'0000' X'9999' X'1234'}
          420  +do_test e_fkey-22.3 {
          421  +  execsql { SELECT quote(c) FROM cA }
          422  +} {X'0000'}
          423  +do_test e_fkey-22.4 {
          424  +  execsql {
          425  +    UPDATE pA SET x = X'8765' WHERE rowid = 4;
          426  +    SELECT quote(x) FROM pA;
          427  +  }
          428  +} {X'0000' X'9999' X'8765'}
          429  +do_test e_fkey-22.5 {
          430  +  execsql { SELECT quote(c) FROM cB }
          431  +} {X'9999'}
          432  +
          433  +#-------------------------------------------------------------------------
          434  +# /* EV: R-61376-57267 */
          435  +# /* EV: R-61809-62207 */
          436  +#
          437  +# Test ON DELETE CASCADE actions.
          438  +#
          439  +drop_all_tables
          440  +do_test e_fkey-23.1 {
          441  +  execsql {
          442  +    CREATE TABLE p1(a, b UNIQUE);
          443  +    CREATE TABLE c1(c REFERENCES p1(b) ON DELETE CASCADE, d);
          444  +    INSERT INTO p1 VALUES(NULL, NULL);
          445  +    INSERT INTO p1 VALUES(4, 4);
          446  +    INSERT INTO p1 VALUES(5, 5);
          447  +    INSERT INTO c1 VALUES(NULL, NULL);
          448  +    INSERT INTO c1 VALUES(4, 4);
          449  +    INSERT INTO c1 VALUES(5, 5);
          450  +    SELECT count(*) FROM c1;
          451  +  }
          452  +} {3}
          453  +do_test e_fkey-23.2 {
          454  +  execsql {
          455  +    DELETE FROM p1 WHERE a = 4;
          456  +    SELECT d, c FROM c1;
          457  +  }
          458  +} {{} {} 5 5}
          459  +do_test e_fkey-23.3 {
          460  +  execsql {
          461  +    DELETE FROM p1;
          462  +    SELECT d, c FROM c1;
          463  +  }
          464  +} {{} {}}
          465  +do_test e_fkey-23.4 {
          466  +  execsql { SELECT * FROM p1 }
          467  +} {}
          468  +
          469  +
          470  +#-------------------------------------------------------------------------
          471  +# /* EV: R-61376-57267 */
          472  +# /* EV: R-13877-64542 */
          473  +#
          474  +# Test ON UPDATE CASCADE actions.
    55    475   #
    56    476   drop_all_tables
    57         -do_test e_fkey-14.1 {
    58         -  execsql {
    59         -    CREATE TABLE artist(
    60         -      artistid    INTEGER PRIMARY KEY, 
    61         -      artistname  TEXT
    62         -    );
    63         -    CREATE TABLE track(
    64         -      trackid     INTEGER,
    65         -      trackname   TEXT, 
    66         -      trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT
    67         -    );
    68         -    INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
    69         -    INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
    70         -  }
    71         -} {}
    72         -do_test e_fkey-14.2 {
    73         -  catchsql { DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.' }
    74         -} {1 {foreign key constraint failed}}
    75         -do_test e_fkey-14.3 {
    76         -  execsql {
    77         -    INSERT INTO artist VALUES(0, 'Unknown Artist');
    78         -    DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
    79         -  }
    80         -} {}
    81         -do_test e_fkey-14.4 {
    82         -  execsql { SELECT * FROM artist }
    83         -} {0 {Unknown Artist}}
    84         -do_test e_fkey-14.5 {
    85         -  execsql { SELECT * FROM track }
    86         -} {14 {Mr. Bojangles} 0}
    87         -
    88         -#-------------------------------------------------------------------------
    89         -# /* EV: R-25213-27898 */
          477  +do_test e_fkey-24.1 {
          478  +  execsql {
          479  +    CREATE TABLE p1(a, b UNIQUE);
          480  +    CREATE TABLE c1(c REFERENCES p1(b) ON UPDATE CASCADE, d);
          481  +    INSERT INTO p1 VALUES(NULL, NULL);
          482  +    INSERT INTO p1 VALUES(4, 4);
          483  +    INSERT INTO p1 VALUES(5, 5);
          484  +    INSERT INTO c1 VALUES(NULL, NULL);
          485  +    INSERT INTO c1 VALUES(4, 4);
          486  +    INSERT INTO c1 VALUES(5, 5);
          487  +    SELECT count(*) FROM c1;
          488  +  }
          489  +} {3}
          490  +do_test e_fkey-24.2 {
          491  +  execsql {
          492  +    UPDATE p1 SET b = 10 WHERE b = 5;
          493  +    SELECT d, c FROM c1;
          494  +  }
          495  +} {{} {} 4 4 5 10}
          496  +do_test e_fkey-24.3 {
          497  +  execsql {
          498  +    UPDATE p1 SET b = 11 WHERE b = 4;
          499  +    SELECT d, c FROM c1;
          500  +  }
          501  +} {{} {} 4 11 5 10}
          502  +do_test e_fkey-24.4 {
          503  +  execsql { 
          504  +    UPDATE p1 SET b = 6 WHERE b IS NULL;
          505  +    SELECT d, c FROM c1;
          506  +  }
          507  +} {{} {} 4 11 5 10}
          508  +do_test e_fkey-23.5 {
          509  +  execsql { SELECT * FROM p1 }
          510  +} {{} 6 4 11 5 10}
          511  +
          512  +#-------------------------------------------------------------------------
          513  +# /* EV: R-51329-33438 */
    90    514   #
    91    515   # Test an example from the "ON DELETE and ON UPDATE Actions" section 
    92    516   # of foreignkeys.html.
    93    517   #
    94    518   drop_all_tables
    95    519   do_test e_fkey-15.1 {
    96    520     execsql {
................................................................................
   119    543   do_test e_fkey-15.3 {
   120    544     execsql { SELECT * FROM artist }
   121    545   } {2 {Frank Sinatra} 100 {Dean Martin}}
   122    546   do_test e_fkey-15.4 {
   123    547     execsql { SELECT * FROM track }
   124    548   } {11 {That's Amore} 100 12 {Christmas Blues} 100 13 {My Way} 2}
   125    549   
          550  +
          551  +#-------------------------------------------------------------------------
          552  +# /* EV: R-53968-51642 */
          553  +#
          554  +# Verify that adding an FK action does not absolve the user of the 
          555  +# requirement not to violate the foreign key constraint.
          556  +#
          557  +drop_all_tables
          558  +do_test e_fkey-25.1 {
          559  +  execsql {
          560  +    CREATE TABLE parent(a COLLATE nocase, b, c, PRIMARY KEY(c, a));
          561  +    CREATE TABLE child(d DEFAULT 'a', e, f DEFAULT 'c',
          562  +      FOREIGN KEY(f, d) REFERENCES parent ON UPDATE SET DEFAULT
          563  +    );
          564  +
          565  +    INSERT INTO parent VALUES('A', 'b', 'c');
          566  +    INSERT INTO parent VALUES('ONE', 'two', 'three');
          567  +    INSERT INTO child VALUES('one', 'two', 'three');
          568  +  }
          569  +} {}
          570  +do_test e_fkey-25.2 {
          571  +  execsql {
          572  +    BEGIN;
          573  +      UPDATE parent SET a = '' WHERE a = 'oNe';
          574  +      SELECT * FROM child;
          575  +  }
          576  +} {a two c}
          577  +do_test e_fkey-25.3 {
          578  +  execsql {
          579  +    ROLLBACK;
          580  +    DELETE FROM parent WHERE a = 'A';
          581  +    SELECT * FROM parent;
          582  +  }
          583  +} {ONE two three}
          584  +do_test e_fkey-25.4 {
          585  +  catchsql { UPDATE parent SET a = '' WHERE a = 'oNe' }
          586  +} {1 {foreign key constraint failed}}
          587  +
          588  +
          589  +#-------------------------------------------------------------------------
          590  +# /* EV: R-07065-59588 */
          591  +# /* EV: R-28220-46694 */
          592  +#
          593  +# Test an example from the "ON DELETE and ON UPDATE Actions" section 
          594  +# of foreignkeys.html. This example shows that adding an "ON DELETE DEFAULT"
          595  +# clause does not abrogate the need to satisfy the foreign key constraint
          596  +# (R-28220-46694).
          597  +#
          598  +drop_all_tables
          599  +do_test e_fkey-14.1 {
          600  +  execsql {
          601  +    CREATE TABLE artist(
          602  +      artistid    INTEGER PRIMARY KEY, 
          603  +      artistname  TEXT
          604  +    );
          605  +    CREATE TABLE track(
          606  +      trackid     INTEGER,
          607  +      trackname   TEXT, 
          608  +      trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT
          609  +    );
          610  +    INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
          611  +    INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
          612  +  }
          613  +} {}
          614  +do_test e_fkey-14.2 {
          615  +  catchsql { DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.' }
          616  +} {1 {foreign key constraint failed}}
          617  +do_test e_fkey-14.3 {
          618  +  execsql {
          619  +    INSERT INTO artist VALUES(0, 'Unknown Artist');
          620  +    DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
          621  +  }
          622  +} {}
          623  +do_test e_fkey-14.4 {
          624  +  execsql { SELECT * FROM artist }
          625  +} {0 {Unknown Artist}}
          626  +do_test e_fkey-14.5 {
          627  +  execsql { SELECT * FROM track }
          628  +} {14 {Mr. Bojangles} 0}
          629  +
          630  +#-------------------------------------------------------------------------
          631  +# /* EV: R-35762-30306 */
          632  +#
          633  +# Check that the order of steps in an UPDATE or DELETE on a parent 
          634  +# table is as follows:
          635  +#
          636  +#   1. Execute applicable BEFORE trigger programs,
          637  +#   2. Check local (non foreign key) constraints,
          638  +#   3. Update or delete the row in the parent table,
          639  +#   4. Perform any required foreign key actions,
          640  +#   5. Execute applicable AFTER trigger programs. 
          641  +#
          642  +drop_all_tables
          643  +do_test e_fkey-27.1 {
          644  +  proc maxparent {args} { db one {SELECT max(x) FROM parent} }
          645  +  db func maxparent maxparent
          646  +
          647  +  execsql {
          648  +    CREATE TABLE parent(x PRIMARY KEY);
          649  +
          650  +    CREATE TRIGGER bu BEFORE UPDATE ON parent BEGIN
          651  +      INSERT INTO parent VALUES(new.x-old.x);
          652  +    END;
          653  +    CREATE TABLE child(
          654  +      a DEFAULT (maxparent()) REFERENCES parent ON UPDATE SET DEFAULT
          655  +    );
          656  +    CREATE TRIGGER au AFTER UPDATE ON parent BEGIN
          657  +      INSERT INTO parent VALUES(new.x+old.x);
          658  +    END;
          659  +
          660  +    INSERT INTO parent VALUES(1);
          661  +    INSERT INTO child VALUES(1);
          662  +  }
          663  +} {}
          664  +do_test e_fkey-27.2 {
          665  +  execsql {
          666  +    UPDATE parent SET x = 22;
          667  +    SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child;
          668  +  }
          669  +} {22 21 23 xxx 22}
          670  +do_test e_fkey-27.3 {
          671  +  execsql {
          672  +    DELETE FROM child;
          673  +    DELETE FROM parent;
          674  +    INSERT INTO parent VALUES(-1);
          675  +    INSERT INTO child VALUES(-1);
          676  +    UPDATE parent SET x = 22;
          677  +    SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child;
          678  +  }
          679  +} {22 23 21 xxx 23}
          680  +
          681  +
          682  +#-------------------------------------------------------------------------
          683  +# /* EV: R-27383-10246 */
          684  +#
          685  +# Verify that ON UPDATE actions only actually take place if the parent key
          686  +# is set to a new value that is distinct from the old value. The default
          687  +# collation sequence and affinity are used to determine if the new value
          688  +# is 'distinct' from the old or not.
          689  +#
          690  +drop_all_tables
          691  +do_test e_fkey-26.1 {
          692  +  execsql {
          693  +    CREATE TABLE zeus(a INTEGER COLLATE NOCASE, b, PRIMARY KEY(a, b));
          694  +    CREATE TABLE apollo(c, d, 
          695  +      FOREIGN KEY(c, d) REFERENCES zeus ON UPDATE CASCADE
          696  +    );
          697  +    INSERT INTO zeus VALUES('abc', 'xyz');
          698  +    INSERT INTO apollo VALUES('ABC', 'xyz');
          699  +  }
          700  +  execsql {
          701  +    UPDATE zeus SET a = 'aBc';
          702  +    SELECT * FROM apollo;
          703  +  }
          704  +} {ABC xyz}
          705  +do_test e_fkey-26.2 {
          706  +  execsql {
          707  +    UPDATE zeus SET a = 1, b = 1;
          708  +    SELECT * FROM apollo;
          709  +  }
          710  +} {1 1}
          711  +do_test e_fkey-26.3 {
          712  +  execsql {
          713  +    UPDATE zeus SET a = 1, b = 1;
          714  +    SELECT typeof(c), c, typeof(d), d FROM apollo;
          715  +  }
          716  +} {integer 1 integer 1}
          717  +do_test e_fkey-26.4 {
          718  +  execsql {
          719  +    UPDATE zeus SET a = '1';
          720  +    SELECT typeof(c), c, typeof(d), d FROM apollo;
          721  +  }
          722  +} {integer 1 integer 1}
          723  +do_test e_fkey-26.5 {
          724  +  execsql {
          725  +    UPDATE zeus SET b = '1';
          726  +    SELECT typeof(c), c, typeof(d), d FROM apollo;
          727  +  }
          728  +} {integer 1 text 1}
          729  +do_test e_fkey-26.6 {
          730  +  execsql {
          731  +    UPDATE zeus SET b = NULL;
          732  +    SELECT typeof(c), c, typeof(d), d FROM apollo;
          733  +  }
          734  +} {integer 1 null {}}
          735  +
          736  +#-------------------------------------------------------------------------
          737  +# /* EV: R-51437-39891 */
          738  +#
          739  +# Test an example from the "ON DELETE and ON UPDATE Actions" section 
          740  +# of foreignkeys.html. This example demonstrates that ON UPDATE actions
          741  +# only take place if at least one parent key column is set to a value 
          742  +# that is distinct from its previous value.
          743  +#
          744  +drop_all_tables
          745  +do_test e_fkey-13.1 {
          746  +  execsql {
          747  +    CREATE TABLE parent(x PRIMARY KEY);
          748  +    CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL);
          749  +    INSERT INTO parent VALUES('key');
          750  +    INSERT INTO child VALUES('key');
          751  +  }
          752  +} {}
          753  +do_test e_fkey-13.2 {
          754  +  execsql {
          755  +    UPDATE parent SET x = 'key';
          756  +    SELECT IFNULL(y, 'null') FROM child;
          757  +  }
          758  +} {key}
          759  +do_test e_fkey-13.3 {
          760  +  execsql {
          761  +    UPDATE parent SET x = 'key2';
          762  +    SELECT IFNULL(y, 'null') FROM child;
          763  +  }
          764  +} {null}
          765  +
          766  +###########################################################################
          767  +### SECTION 5: CREATE, ALTER and DROP TABLE commands
          768  +###########################################################################
          769  +
   126    770   #-------------------------------------------------------------------------
   127    771   # /* EV: R-36018-21755 */
   128    772   # /* EV: R-25384-39337 */
   129    773   # 
   130    774   # Test that parent keys are not checked when tables are created.
   131    775   #
   132    776   # Child keys are checked to ensure all component columns exist. If parent
................................................................................
   474   1118       SELECT * FROM c;
   475   1119     }
   476   1120   } {x}
   477   1121   do_test e_fkey-12.3.3 {
   478   1122     execsql { PRAGMA foreign_keys = ON }
   479   1123   } {}
   480   1124   
         1125  +###########################################################################
         1126  +### SECTION 6: Limits and Unsupported Features
         1127  +###########################################################################
   481   1128   
   482   1129   #-------------------------------------------------------------------------
   483   1130   # /* EV: R-24728-13230 */
   484   1131   # /* EV: R-24450-46174 */
   485   1132   #
   486   1133   # Test that MATCH clauses are parsed, but SQLite treats every foreign key
   487   1134   # constraint as if it were "MATCH SIMPLE".