/ Check-in [d61cc0e1]
Login

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

Overview
Comment:Add further test cases to e_fkey.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d61cc0e1a1e8b4bf49016d3d14554f9c20f86f6b
User & Date: dan 2009-10-12 08:41:50
Context
2009-10-12
11:27
Extra test cases mapped to statements in foreignkeys.html. check-in: ffa6207d user: dan tags: trunk
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_fkey.test.

    18     18   ifcapable {!foreignkey} { finish_test ; return }
    19     19   
    20     20   execsql "PRAGMA foreign_keys = ON"
    21     21   
    22     22   ###########################################################################
    23     23   ### SECTION 1: Introduction to Foreign Key Constraints
    24     24   ###########################################################################
           25  +
           26  +#-------------------------------------------------------------------------
           27  +# /* EV: R-04042-24825 */
           28  +#
           29  +# Verify that the syntax in the first example in section 1 is valid.
           30  +#
           31  +do_test e_fkey-38.1 {
           32  +  execsql {
           33  +    CREATE TABLE artist(
           34  +      artistid    INTEGER PRIMARY KEY, 
           35  +      artistname  TEXT
           36  +    );
           37  +    CREATE TABLE track(
           38  +      trackid     INTEGER, 
           39  +      trackname   TEXT, 
           40  +      trackartist INTEGER,
           41  +      FOREIGN KEY(trackartist) REFERENCES artist(artistid)
           42  +    );
           43  +  }
           44  +} {}
           45  +
           46  +#-------------------------------------------------------------------------
           47  +# /* EV: R-61362-32087 */
           48  +#
           49  +# Attempting to insert a row into the 'track' table that corresponds
           50  +# to no row in the 'artist' table fails.
           51  +#
           52  +do_test e_fkey-39.1 {
           53  +  catchsql { INSERT INTO track VALUES(1, 'track 1', 1) }
           54  +} {1 {foreign key constraint failed}}
           55  +do_test e_fkey-39.2 {
           56  +  execsql { INSERT INTO artist VALUES(2, 'artist 1') }
           57  +  catchsql { INSERT INTO track VALUES(1, 'track 1', 1) }
           58  +} {1 {foreign key constraint failed}}
           59  +do_test e_fkey-39.2 {
           60  +  execsql { INSERT INTO track VALUES(1, 'track 1', 2) }
           61  +} {}
           62  +
           63  +#-------------------------------------------------------------------------
           64  +# /* EV: R-24401-52400 */
           65  +#
           66  +# Attempting to delete a row from the 'artist' table while there are 
           67  +# dependent rows in the track table also fails.
           68  +#
           69  +do_test e_fkey-40.1 {
           70  +  catchsql { DELETE FROM artist WHERE artistid = 2 }
           71  +} {1 {foreign key constraint failed}}
           72  +do_test e_fkey-40.2 {
           73  +  execsql { 
           74  +    DELETE FROM track WHERE trackartist = 2;
           75  +    DELETE FROM artist WHERE artistid = 2;
           76  +  }
           77  +} {}
           78  +
           79  +#-------------------------------------------------------------------------
           80  +# /* EV: R-23980-48859 */
           81  +#
           82  +# If the foreign key column (trackartist) in table 'track' is set to NULL,
           83  +# there is no requirement for a matching row in the 'artist' table.
           84  +#
           85  +do_test e_fkey-41.1 {
           86  +  execsql {
           87  +    INSERT INTO track VALUES(1, 'track 1', NULL);
           88  +    INSERT INTO track VALUES(2, 'track 2', NULL);
           89  +  }
           90  +} {}
           91  +do_test e_fkey-41.2 {
           92  +  execsql { SELECT * FROM artist }
           93  +} {}
           94  +do_test e_fkey-41.3 {
           95  +  # Setting the trackid to a non-NULL value fails, of course.
           96  +  catchsql { UPDATE track SET trackartist = 5 WHERE trackid = 1 }
           97  +} {1 {foreign key constraint failed}}
           98  +do_test e_fkey-41.4 {
           99  +  execsql {
          100  +    INSERT INTO artist VALUES(5, 'artist 5');
          101  +    UPDATE track SET trackartist = 5 WHERE trackid = 1;
          102  +  }
          103  +  catchsql { DELETE FROM artist WHERE artistid = 5}
          104  +} {1 {foreign key constraint failed}}
          105  +do_test e_fkey-41.5 {
          106  +  execsql { 
          107  +    UPDATE track SET trackartist = NULL WHERE trackid = 1;
          108  +    DELETE FROM artist WHERE artistid = 5;
          109  +  }
          110  +} {}
          111  +
          112  +#-------------------------------------------------------------------------
          113  +# /* EV: R-52486-21352 */
          114  +#
          115  +# Test that the following is true fo all rows in the track table:
          116  +#
          117  +#   trackartist IS NULL OR 
          118  +#   EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
          119  +#
          120  +
          121  +# This procedure executes a test case to check that statement 
          122  +# R-52486-21352 is true after executing the SQL statement passed.
          123  +# as the second argument.
          124  +proc test_r52486_21352 {tn sql} {
          125  +  set res [catchsql $sql]
          126  +  set results {
          127  +    {0 {}} 
          128  +    {1 {PRIMARY KEY must be unique}} 
          129  +    {1 {foreign key constraint failed}}
          130  +  }
          131  +  if {[lsearch $results $res]<0} {
          132  +    error $res
          133  +  }
          134  +
          135  +  do_test e_fkey-42.$tn {
          136  +    execsql {
          137  +      SELECT count(*) FROM track WHERE NOT (
          138  +        trackartist IS NULL OR 
          139  +        EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
          140  +      )
          141  +    }
          142  +  } {0}
          143  +}
          144  +
          145  +# Execute a series of random INSERT, UPDATE and DELETE operations
          146  +# (some of which may fail due to FK or PK constraint violations) on 
          147  +# the two tables in the example schema. Test that R-52486-21352
          148  +# is true after executing each operation.
          149  +#
          150  +set Template {
          151  +  {INSERT INTO track VALUES($t, 'track $t', $a)}
          152  +  {DELETE FROM track WHERE trackid = $t}
          153  +  {UPDATE track SET trackartist = $a WHERE trackid = $t}
          154  +  {INSERT INTO artist VALUES($a, 'artist $a')}
          155  +  {DELETE FROM artist WHERE artistid = $a}
          156  +  {UPDATE artist SET artistid = $a2 WHERE artistid = $a}
          157  +}
          158  +for {set i 0} {$i < 500} {incr i} {
          159  +  set a   [expr int(rand()*10)]
          160  +  set a2  [expr int(rand()*10)]
          161  +  set t   [expr int(rand()*50)]
          162  +  set sql [subst [lindex $Template [expr int(rand()*6)]]]
          163  +
          164  +  test_r52486_21352 $i $sql
          165  +}
          166  +
          167  +#-------------------------------------------------------------------------
          168  +# /* EV: R-42412-59321 */
          169  +#
          170  +# Check that a NOT NULL constraint can be added to the example schema
          171  +# to prohibit NULL child keys from being inserted.
          172  +#
          173  +drop_all_tables
          174  +do_test e_fkey-48.1 {
          175  +  execsql {
          176  +    CREATE TABLE artist(
          177  +      artistid    INTEGER PRIMARY KEY, 
          178  +      artistname  TEXT
          179  +    );
          180  +    CREATE TABLE track(
          181  +      trackid     INTEGER, 
          182  +      trackname   TEXT, 
          183  +      trackartist INTEGER NOT NULL,
          184  +      FOREIGN KEY(trackartist) REFERENCES artist(artistid)
          185  +    );
          186  +  }
          187  +} {}
          188  +do_test e_fkey-48.2 {
          189  +  catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) }
          190  +} {1 {track.trackartist may not be NULL}}
          191  +
          192  +#-------------------------------------------------------------------------
          193  +# /* EV: R-01991-24099 */
          194  +#
          195  +# Test an example from foreignkeys.html.
          196  +#
          197  +drop_all_tables
          198  +do_test e_fkey-43.1 {
          199  +  execsql {
          200  +    CREATE TABLE artist(
          201  +      artistid    INTEGER PRIMARY KEY, 
          202  +      artistname  TEXT
          203  +    );
          204  +    CREATE TABLE track(
          205  +      trackid     INTEGER, 
          206  +      trackname   TEXT, 
          207  +      trackartist INTEGER,
          208  +      FOREIGN KEY(trackartist) REFERENCES artist(artistid)
          209  +    );
          210  +    INSERT INTO artist VALUES(1, 'Dean Martin');
          211  +    INSERT INTO artist VALUES(2, 'Frank Sinatra');
          212  +    INSERT INTO track VALUES(11, 'That''s Amore', 1);
          213  +    INSERT INTO track VALUES(12, 'Christmas Blues', 1);
          214  +    INSERT INTO track VALUES(13, 'My Way', 2);
          215  +  }
          216  +} {}
          217  +do_test e_fkey-43.2 {
          218  +  catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', 3) }
          219  +} {1 {foreign key constraint failed}}
          220  +do_test e_fkey-43.3 {
          221  +  execsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) }
          222  +} {}
          223  +do_test e_fkey-43.4 {
          224  +  catchsql { 
          225  +    UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
          226  +  }
          227  +} {1 {foreign key constraint failed}}
          228  +do_test e_fkey-43.5 {
          229  +  execsql {
          230  +    INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
          231  +    UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
          232  +    INSERT INTO track VALUES(15, 'Boogie Woogie', 3);
          233  +  }
          234  +} {}
          235  +
          236  +#-------------------------------------------------------------------------
          237  +# /* EV: R-22377-02546 */
          238  +#
          239  +# Test the second example from the first section of foreignkeys.html.
          240  +#
          241  +do_test e_fkey-44.1 {
          242  +  catchsql {
          243  +    DELETE FROM artist WHERE artistname = 'Frank Sinatra';
          244  +  }
          245  +} {1 {foreign key constraint failed}}
          246  +do_test e_fkey-44.2 {
          247  +  execsql {
          248  +    DELETE FROM track WHERE trackname = 'My Way';
          249  +    DELETE FROM artist WHERE artistname = 'Frank Sinatra';
          250  +  }
          251  +} {}
          252  +do_test e_fkey-44.3 {
          253  +  catchsql {
          254  +    UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
          255  +  }
          256  +} {1 {foreign key constraint failed}}
          257  +do_test e_fkey-44.4 {
          258  +  execsql {
          259  +    DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');
          260  +    UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
          261  +  }
          262  +} {}
          263  +
          264  +
          265  +#-------------------------------------------------------------------------
          266  +# /* EV: R-56032-24923 */
          267  +#
          268  +# Test that a foreign key constraint is satisifed if "for each row in the child
          269  +# table either one or more of the child key columns are NULL, or there exists a
          270  +# row in the parent table for which each parent key column contains a value
          271  +# equal to the value in its associated child key column".
          272  +#
          273  +# /* EV: R-57765-12380 */
          274  +#
          275  +# Test also that the comparison rules are used when testing if there 
          276  +# is a matching row in the parent table of a foreign key constraint.
          277  +#
          278  +drop_all_tables
          279  +do_test e_fkey-45.1 {
          280  +  execsql {
          281  +    CREATE TABLE par(p PRIMARY KEY);
          282  +    CREATE TABLE chi(c REFERENCES par);
          283  +
          284  +    INSERT INTO par VALUES(1);
          285  +    INSERT INTO par VALUES('1');
          286  +    INSERT INTO par VALUES(X'31');
          287  +    SELECT typeof(p) FROM par;
          288  +  }
          289  +} {integer text blob}
          290  +
          291  +proc test_efkey_45 {tn isError sql} {
          292  +  do_test e_fkey-45.$tn.1 "
          293  +    catchsql {$sql}
          294  +  " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError]
          295  +
          296  +  do_test e_fkey-45.$tn.2 {
          297  +    execsql {
          298  +      SELECT * FROM chi WHERE c IS NOT NULL AND c NOT IN (SELECT p FROM par)
          299  +    }
          300  +  } {}
          301  +}
          302  +
          303  +test_efkey_45 1 0 "INSERT INTO chi VALUES(1)"
          304  +test_efkey_45 2 1 "INSERT INTO chi VALUES('1.0')"
          305  +test_efkey_45 3 0 "INSERT INTO chi VALUES('1')"
          306  +test_efkey_45 4 1 "DELETE FROM par WHERE p = '1'"
          307  +test_efkey_45 5 0 "DELETE FROM chi WHERE c = '1'"
          308  +test_efkey_45 6 0 "DELETE FROM par WHERE p = '1'"
          309  +test_efkey_45 7 1 "INSERT INTO chi VALUES('1')"
          310  +test_efkey_45 8 0 "INSERT INTO chi VALUES(X'31')"
          311  +test_efkey_45 9 1 "INSERT INTO chi VALUES(X'32')"
          312  +
          313  +#-------------------------------------------------------------------------
          314  +# /* EV: R-15796-47513 */
          315  +#
          316  +# Specifically, test that when comparing child and parent key values the
          317  +# default collation sequence of the parent key column is used.
          318  +#
          319  +drop_all_tables
          320  +do_test e_fkey-46.1 {
          321  +  execsql {
          322  +    CREATE TABLE t1(a COLLATE nocase PRIMARY KEY);
          323  +    CREATE TABLE t2(b REFERENCES t1);
          324  +  }
          325  +} {}
          326  +do_test e_fkey-46.2 {
          327  +  execsql {
          328  +    INSERT INTO t1 VALUES('oNe');
          329  +    INSERT INTO t2 VALUES('one');
          330  +    INSERT INTO t2 VALUES('ONE');
          331  +    UPDATE t2 SET b = 'OnE';
          332  +    UPDATE t1 SET a = 'ONE';
          333  +  }
          334  +} {}
          335  +do_test e_fkey-46.3 {
          336  +  catchsql { UPDATE t2 SET b = 'two' WHERE rowid = 1 }
          337  +} {1 {foreign key constraint failed}}
          338  +do_test e_fkey-46.4 {
          339  +  catchsql { DELETE FROM t1 WHERE rowid = 1 }
          340  +} {1 {foreign key constraint failed}}
          341  +
          342  +#-------------------------------------------------------------------------
          343  +# /* EV: R-04240-13860 */
          344  +#
          345  +# Specifically, test that when comparing child and parent key values the
          346  +# affinity of the parent key column is applied to the child key value
          347  +# before the comparison takes place.
          348  +#
          349  +drop_all_tables
          350  +do_test e_fkey-47.1 {
          351  +  execsql {
          352  +    CREATE TABLE t1(a NUMERIC PRIMARY KEY);
          353  +    CREATE TABLE t2(b TEXT REFERENCES t1);
          354  +  }
          355  +} {}
          356  +do_test e_fkey-47.2 {
          357  +  execsql {
          358  +    INSERT INTO t1 VALUES(1);
          359  +    INSERT INTO t1 VALUES(2);
          360  +    INSERT INTO t1 VALUES('three');
          361  +    INSERT INTO t2 VALUES('2.0');
          362  +    SELECT b, typeof(b) FROM t2;
          363  +  }
          364  +} {2.0 text}
          365  +do_test e_fkey-47.3 {
          366  +  execsql { SELECT typeof(a) FROM t1 }
          367  +} {integer integer text}
          368  +do_test e_fkey-47.4 {
          369  +  catchsql { DELETE FROM t1 WHERE rowid = 2 }
          370  +} {1 {foreign key constraint failed}}
    25    371   
    26    372   ###########################################################################
    27    373   ### SECTION 2: Enabling Foreign Key Support
    28    374   ###########################################################################
    29    375   
    30    376   ###########################################################################
    31    377   ### SECTION 3: Required and Suggested Database Indexes
    32    378   ###########################################################################
    33    379   
    34    380   ###########################################################################
    35    381   ### SECTION 4.1: Composite Foreign Key Constraints
    36    382   ###########################################################################
          383  +
          384  +#-------------------------------------------------------------------------
          385  +# /* EV: R-56396-15644 */
          386  +#
          387  +# Test the example schema in the "Composite Foreign Key Constraints" 
          388  +# section.
          389  +#
          390  +do_test e_fkey-36.1 {
          391  +  execsql {
          392  +    CREATE TABLE album(
          393  +      albumartist TEXT,
          394  +      albumname TEXT,
          395  +      albumcover BINARY,
          396  +      PRIMARY KEY(albumartist, albumname)
          397  +    );
          398  +    CREATE TABLE song(
          399  +      songid INTEGER,
          400  +      songartist TEXT,
          401  +      songalbum TEXT,
          402  +      songname TEXT,
          403  +      FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist,albumname)
          404  +    );
          405  +  }
          406  +} {}
          407  +
          408  +do_test e_fkey-36.2 {
          409  +  execsql {
          410  +    INSERT INTO album VALUES('Elvis Presley', 'Elvis'' Christmas Album', NULL);
          411  +    INSERT INTO song VALUES(
          412  +      1, 'Elvis Presley', 'Elvis'' Christmas Album', 'Here Comes Santa Clause'
          413  +    );
          414  +  }
          415  +} {}
          416  +do_test e_fkey-36.3 {
          417  +  catchsql {
          418  +    INSERT INTO song VALUES(2, 'Elvis Presley', 'Elvis Is Back!', 'Fever');
          419  +  }
          420  +} {1 {foreign key constraint failed}}
          421  +
          422  +#-------------------------------------------------------------------------
          423  +# /* EV: R-33626-48418 */
          424  +#
          425  +# Check that if any of the child key columns in the above schema are NULL,
          426  +# there is no requirement for a corresponding parent key.
          427  +#
          428  +do_test e_fkey-37.1 {
          429  +  execsql {
          430  +    INSERT INTO song VALUES(2, 'Elvis Presley', NULL, 'Fever');
          431  +    INSERT INTO song VALUES(3, NULL, 'Elvis Is Back', 'Soldier Boy');
          432  +  }
          433  +} {}
    37    434   
    38    435   ###########################################################################
    39    436   ### SECTION 4.2: Deferred Foreign Key Constraints
    40    437   ###########################################################################
    41    438   
    42    439   #-------------------------------------------------------------------------
    43    440   # Note: R-35290-16460 is tested below.
          441  +#
    44    442   # TODO: R-30323-21917
    45    443   
    46    444   #-------------------------------------------------------------------------
    47    445   # /* EV: R-09323-30470 */
    48    446   #
    49    447   # Test that if a statement violates an immediate FK constraint, and the
    50    448   # database does not satisfy the FK constraint once all effects of the