/ Check-in [c85e5874]
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: c85e58743a87bfbea6376c237e5e3a6909184f6c
User & Date: dan 2009-10-13 10:39:17
Context
2009-10-13
12:48
Make sure mem3.c releases its mutex on an sqlite3_shutdown() call. check-in: 16254ad5 user: drh tags: trunk
10:39
Add further test cases to e_fkey.test. check-in: c85e5874 user: dan tags: trunk
2009-10-12
22:30
Add asserts as evidence that all FK constraints are either immediate or deferred. check-in: 634ef4fc user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_fkey.test.

    20     20   # either SQLITE_OMIT_TRIGGER or SQLITE_OMIT_FOREIGN_KEY was defined
    21     21   # at build time).
    22     22   #
    23     23   
    24     24   set testdir [file dirname $argv0]
    25     25   source $testdir/tester.tcl
    26     26   
           27  +proc eqp {sql {db db}} { uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db }
           28  +
    27     29   ###########################################################################
    28     30   ### SECTION 2: Enabling Foreign Key Support
    29     31   ###########################################################################
    30     32   
    31     33   #-------------------------------------------------------------------------
    32     34   # /* EV: R-33710-56344 */
    33     35   #
................................................................................
   665    667     catchsql { INSERT INTO child6 VALUES(2, 3) }
   666    668   } {1 {foreign key mismatch}}
   667    669   do_test e_fkey-56.5 {
   668    670     catchsql { INSERT INTO child7 VALUES(3) }
   669    671   } {1 {foreign key mismatch}}
   670    672   
   671    673   #-------------------------------------------------------------------------
          674  +# /* EV: R-45488-08504 */
          675  +# /* EV: R-48391-38472 */
   672    676   # /* EV: R-03108-63659 */
   673    677   # /* EV: R-60781-26576 */
   674    678   #
   675    679   # Test errors in the database schema that are detected while preparing
   676    680   # DML statements. The error text for these messages always matches 
   677    681   # either "foreign key mismatch" or "no such table*" (using [string match]).
   678    682   #
   679         -do_test e_fkey-57.1 {
          683  +do_test e_fkey-66.1 {
   680    684     execsql {
   681    685       CREATE TABLE c1(c REFERENCES nosuchtable, d);
   682    686   
   683    687       CREATE TABLE p2(a, b, UNIQUE(a, b));
   684    688       CREATE TABLE c2(c, d, FOREIGN KEY(c, d) REFERENCES p2(a, x));
   685    689   
   686    690       CREATE TABLE p3(a PRIMARY KEY, b);
................................................................................
   688    692   
   689    693       CREATE TABLE p4(a PRIMARY KEY, b);
   690    694       CREATE UNIQUE INDEX p4i ON p4(b COLLATE nocase);
   691    695       CREATE TABLE c4(c REFERENCES p4(b), d);
   692    696   
   693    697       CREATE TABLE p5(a PRIMARY KEY, b COLLATE nocase);
   694    698       CREATE UNIQUE INDEX p5i ON p5(b COLLATE binary);
   695         -    CREATE TABLE c5(c REFERENCES p4(b), d);
          699  +    CREATE TABLE c5(c REFERENCES p5(b), d);
   696    700   
   697    701       CREATE TABLE p6(a PRIMARY KEY, b);
   698    702       CREATE TABLE c6(c, d, FOREIGN KEY(c, d) REFERENCES p6);
   699    703   
   700    704       CREATE TABLE p7(a, b, PRIMARY KEY(a, b));
   701    705       CREATE TABLE c7(c, d REFERENCES p7);
   702    706     }
   703    707   } {}
   704    708   
   705         -foreach {tn tbl err} {
   706         -  2 c1 "no such table: main.nosuchtable"
   707         -  3 c2 "foreign key mismatch"
   708         -  4 c3 "foreign key mismatch"
   709         -  5 c4 "foreign key mismatch"
   710         -  6 c5 "foreign key mismatch"
   711         -  7 c6 "foreign key mismatch"
   712         -  8 c7 "foreign key mismatch"
          709  +foreach {tn tbl ptbl err} {
          710  +  2 c1 {} "no such table: main.nosuchtable"
          711  +  3 c2 p2 "foreign key mismatch"
          712  +  4 c3 p3 "foreign key mismatch"
          713  +  5 c4 p4 "foreign key mismatch"
          714  +  6 c5 p5 "foreign key mismatch"
          715  +  7 c6 p6 "foreign key mismatch"
          716  +  8 c7 p7 "foreign key mismatch"
   713    717   } {
   714         -  do_test e_fkey-57.$tn {
          718  +  do_test e_fkey-66.$tn.1 {
   715    719       catchsql "INSERT INTO $tbl VALUES('a', 'b')"
   716    720     } [list 1 $err]
          721  +  do_test e_fkey-66.$tn.2 {
          722  +    catchsql "UPDATE $tbl SET c = ?, d = ?"
          723  +  } [list 1 $err]
          724  +  do_test e_fkey-66.$tn.3 {
          725  +    catchsql "INSERT INTO $tbl SELECT ?, ?"
          726  +  } [list 1 $err]
          727  +
          728  +  if {$ptbl ne ""} {
          729  +    do_test e_fkey-66.$tn.4 {
          730  +      catchsql "DELETE FROM $ptbl"
          731  +    } [list 1 $err]
          732  +    do_test e_fkey-66.$tn.5 {
          733  +      catchsql "UPDATE $ptbl SET a = ?, b = ?"
          734  +    } [list 1 $err]
          735  +    do_test e_fkey-66.$tn.6 {
          736  +      catchsql "INSERT INTO $ptbl SELECT ?, ?"
          737  +    } [list 1 $err]
          738  +  }
   717    739   }
   718    740   
   719    741   #-------------------------------------------------------------------------
   720    742   # /* EV: R-19353-43643 */
   721    743   #
   722    744   # Test the example of foreign key mismatch errors caused by implicitly
   723    745   # mapping a child key to the primary key of the parent table when the
................................................................................
   814    836   test_efkey_60 2 1 "INSERT INTO c1 VALUES(239, 231)"
   815    837   test_efkey_60 3 0 "INSERT INTO p1 VALUES(239, 231)"
   816    838   test_efkey_60 4 0 "INSERT INTO c1 VALUES(239, 231)"
   817    839   test_efkey_60 5 1 "INSERT INTO c2 VALUES(239, 231)"
   818    840   test_efkey_60 6 0 "INSERT INTO p2 VALUES(239, 231)"
   819    841   test_efkey_60 7 0 "INSERT INTO c2 VALUES(239, 231)"
   820    842   
          843  +#-------------------------------------------------------------------------
          844  +# /* EV: R-15417-28014 */
          845  +#
          846  +# Test that an index on on the child key columns of an FK constraint
          847  +# is optional.
          848  +#
          849  +# /* EV: R-15741-50893 */
          850  +#
          851  +# Also test that if an index is created on the child key columns, it does
          852  +# not make a difference whether or not it is a UNIQUE index.
          853  +#
          854  +drop_all_tables
          855  +do_test e_fkey-61.1 {
          856  +  execsql {
          857  +    CREATE TABLE parent(x, y, UNIQUE(y, x));
          858  +    CREATE TABLE c1(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
          859  +    CREATE TABLE c2(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
          860  +    CREATE TABLE c3(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
          861  +    CREATE INDEX c2i ON c2(a, b);
          862  +    CREATE UNIQUE INDEX c3i ON c2(b, a);
          863  +  }
          864  +} {}
          865  +proc test_efkey_61 {tn isError sql} {
          866  +  do_test e_fkey-61.$tn "
          867  +    catchsql {$sql}
          868  +  " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError]
          869  +}
          870  +foreach {tn c} [list 2 c1 3 c2 4 c3] {
          871  +  test_efkey_61 $tn.1 1 "INSERT INTO $c VALUES(1, 2)"
          872  +  test_efkey_61 $tn.2 0 "INSERT INTO parent VALUES(1, 2)"
          873  +  test_efkey_61 $tn.3 0 "INSERT INTO $c VALUES(1, 2)"
          874  +
          875  +  execsql "DELETE FROM $c ; DELETE FROM parent"
          876  +}
          877  +
          878  +#-------------------------------------------------------------------------
          879  +# /* EV: R-00279-52283 */
          880  +#
          881  +# Test an example showing that when a row is deleted from the parent 
          882  +# table, the child table is queried for orphaned rows as follows:
          883  +#
          884  +#   SELECT rowid FROM track WHERE trackartist = ?
          885  +#
          886  +# /* EV: R-23302-30956 */
          887  +#
          888  +# Also test that if the SELECT above would return any rows, a foreign
          889  +# key constraint is violated.
          890  +#
          891  +do_test e_fkey-62.1 {
          892  +  execsql {
          893  +    CREATE TABLE artist(
          894  +      artistid    INTEGER PRIMARY KEY, 
          895  +      artistname  TEXT
          896  +    );
          897  +    CREATE TABLE track(
          898  +      trackid     INTEGER, 
          899  +      trackname   TEXT, 
          900  +      trackartist INTEGER,
          901  +      FOREIGN KEY(trackartist) REFERENCES artist(artistid)
          902  +    );
          903  +  }
          904  +} {}
          905  +do_test e_fkey-62.2 {
          906  +  execsql {
          907  +    PRAGMA foreign_keys = OFF;
          908  +    EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
          909  +    EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?;
          910  +  }
          911  +} {0 0 {TABLE artist} 0 0 {TABLE track}}
          912  +do_test e_fkey-62.3 {
          913  +  execsql { 
          914  +    PRAGMA foreign_keys = ON;
          915  +    EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
          916  +  }
          917  +} {0 0 {TABLE artist} 0 0 {TABLE track}}
          918  +do_test e_fkey-62.4 {
          919  +  execsql {
          920  +    INSERT INTO artist VALUES(5, 'artist 5');
          921  +    INSERT INTO artist VALUES(6, 'artist 6');
          922  +    INSERT INTO artist VALUES(7, 'artist 7');
          923  +    INSERT INTO track VALUES(1, 'track 1', 5);
          924  +    INSERT INTO track VALUES(2, 'track 2', 6);
          925  +  }
          926  +} {}
          927  +
          928  +do_test e_fkey-62.5 {
          929  +  concat \
          930  +    [execsql { SELECT rowid FROM track WHERE trackartist = 5 }]   \
          931  +    [catchsql { DELETE FROM artist WHERE artistid = 5 }]
          932  +} {1 1 {foreign key constraint failed}}
          933  +
          934  +do_test e_fkey-62.6 {
          935  +  concat \
          936  +    [execsql { SELECT rowid FROM track WHERE trackartist = 7 }]   \
          937  +    [catchsql { DELETE FROM artist WHERE artistid = 7 }]
          938  +} {0 {}}
          939  +
          940  +do_test e_fkey-62.7 {
          941  +  concat \
          942  +    [execsql { SELECT rowid FROM track WHERE trackartist = 6 }]   \
          943  +    [catchsql { DELETE FROM artist WHERE artistid = 6 }]
          944  +} {2 1 {foreign key constraint failed}}
          945  +
          946  +#-------------------------------------------------------------------------
          947  +# /* EV: R-54172-55848 */
          948  +#
          949  +# Test that when a row is deleted from the parent table of an FK 
          950  +# constraint, the child table is queried for orphaned rows. The
          951  +# query is equivalent to:
          952  +#
          953  +#   SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
          954  +#
          955  +# /* EV: R-61616-46700 */
          956  +#
          957  +# Also test that when a row is inserted into the parent table, or when the 
          958  +# parent key values of an existing row are modified, a query equivalent
          959  +# to the following is planned. In some cases it is not executed, but it
          960  +# is always planned.
          961  +#
          962  +#   SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
          963  +#
          964  +#
          965  +drop_all_tables
          966  +do_test e_fkey-64.1 {
          967  +  execsql { CREATE TABLE parent(x, y, UNIQUE(y, x)) }
          968  +} {}
          969  +foreach {tn sql} {
          970  +  2 { 
          971  +    CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y))
          972  +  }
          973  +  3 { 
          974  +    CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
          975  +    CREATE INDEX childi ON child(a, b);
          976  +  }
          977  +  4 { 
          978  +    CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
          979  +    CREATE UNIQUE INDEX childi ON child(b, a);
          980  +  }
          981  +} {
          982  +  execsql $sql
          983  +
          984  +  execsql {PRAGMA foreign_keys = OFF}
          985  +  set delete [concat \
          986  +      [eqp "DELETE FROM parent WHERE 1"] \
          987  +      [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"]
          988  +  ]
          989  +  set update [concat \
          990  +      [eqp "UPDATE parent SET x=?, y=?"] \
          991  +      [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"] \
          992  +      [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"]
          993  +  ]
          994  +  execsql {PRAGMA foreign_keys = ON}
          995  +
          996  +  do_test e_fkey-64.$tn.1 { eqp "DELETE FROM parent WHERE 1" } $delete
          997  +  do_test e_fkey-64.$tn.2 { eqp "UPDATE parent set x=?, y=?" } $update
          998  +
          999  +  execsql {DROP TABLE child}
         1000  +}
         1001  +
         1002  +#-------------------------------------------------------------------------
         1003  +# /* EV: R-14553-34013 */
         1004  +#
         1005  +# Test the example schema at the end of section 3. Also test that is
         1006  +# is "efficient". In this case "efficient" means that foreign key
         1007  +# related operations on the parent table do not provoke linear scans.
         1008  +#
         1009  +drop_all_tables
         1010  +do_test e_fkey-63.1 {
         1011  +  execsql {
         1012  +    CREATE TABLE artist(
         1013  +      artistid    INTEGER PRIMARY KEY, 
         1014  +      artistname  TEXT
         1015  +    );
         1016  +    CREATE TABLE track(
         1017  +      trackid     INTEGER,
         1018  +      trackname   TEXT, 
         1019  +      trackartist INTEGER REFERENCES artist
         1020  +    );
         1021  +    CREATE INDEX trackindex ON track(trackartist);
         1022  +  }
         1023  +} {}
         1024  +do_test e_fkey-63.2 {
         1025  +  eqp { INSERT INTO artist VALUES(?, ?) }
         1026  +} {}
         1027  +do_test e_fkey-63.3 {
         1028  +  eqp { UPDATE artist SET artistid = ?, artistname = ? }
         1029  +} [list \
         1030  +  0 0 {TABLE artist} \
         1031  +  0 0 {TABLE track WITH INDEX trackindex} \
         1032  +  0 0 {TABLE track WITH INDEX trackindex}
         1033  +]
         1034  +do_test e_fkey-63.4 {
         1035  +  eqp { DELETE FROM artist }
         1036  +} [list \
         1037  +  0 0 {TABLE artist} \
         1038  +  0 0 {TABLE track WITH INDEX trackindex}
         1039  +]
         1040  +
   821   1041   
   822   1042   ###########################################################################
   823   1043   ### SECTION 4.1: Composite Foreign Key Constraints
   824   1044   ###########################################################################
         1045  +
         1046  +#-------------------------------------------------------------------------
         1047  +# /* EV: R-41062-34431 */
         1048  +#
         1049  +# Check that parent and child keys must have the same number of columns.
         1050  +#
         1051  +foreach {tn sql err} {
         1052  +  1 "CREATE TABLE c(jj REFERENCES p(x, y))" 
         1053  +    {foreign key on jj should reference only one column of table p}
         1054  +
         1055  +  2 "CREATE TABLE c(jj REFERENCES p())" {near ")": syntax error}
         1056  +
         1057  +  3 "CREATE TABLE c(jj, FOREIGN KEY(jj) REFERENCES p(x, y))" 
         1058  +    {number of columns in foreign key does not match the number of columns in the referenced table}
         1059  +
         1060  +  4 "CREATE TABLE c(jj, FOREIGN KEY(jj) REFERENCES p())" 
         1061  +    {near ")": syntax error}
         1062  +
         1063  +  5 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p())" 
         1064  +    {near ")": syntax error}
         1065  +
         1066  +  6 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x))" 
         1067  +    {number of columns in foreign key does not match the number of columns in the referenced table}
         1068  +
         1069  +  7 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x,y,z))" 
         1070  +    {number of columns in foreign key does not match the number of columns in the referenced table}
         1071  +} {
         1072  +  drop_all_tables
         1073  +  do_test e_fkey-65.$tn [list catchsql $sql] [list 1 $err]
         1074  +}
         1075  +do_test e_fkey-65.8 {
         1076  +  drop_all_tables
         1077  +  execsql {
         1078  +    CREATE TABLE p(x PRIMARY KEY);
         1079  +    CREATE TABLE c(a, b, FOREIGN KEY(a,b) REFERENCES p);
         1080  +  }
         1081  +  catchsql {DELETE FROM p}
         1082  +} {1 {foreign key mismatch}}
         1083  +do_test e_fkey-65.9 {
         1084  +  drop_all_tables
         1085  +  execsql {
         1086  +    CREATE TABLE p(x, y, PRIMARY KEY(x,y));
         1087  +    CREATE TABLE c(a REFERENCES p);
         1088  +  }
         1089  +  catchsql {DELETE FROM p}
         1090  +} {1 {foreign key mismatch}}
         1091  +
   825   1092   
   826   1093   #-------------------------------------------------------------------------
   827   1094   # /* EV: R-24676-09859 */
   828   1095   #
   829   1096   # Test the example schema in the "Composite Foreign Key Constraints" 
   830   1097   # section.
   831   1098   #
................................................................................
   856   1123     }
   857   1124   } {}
   858   1125   do_test e_fkey-36.3 {
   859   1126     catchsql {
   860   1127       INSERT INTO song VALUES(2, 'Elvis Presley', 'Elvis Is Back!', 'Fever');
   861   1128     }
   862   1129   } {1 {foreign key constraint failed}}
         1130  +
   863   1131   
   864   1132   #-------------------------------------------------------------------------
   865   1133   # /* EV: R-33626-48418 */
   866   1134   #
   867   1135   # Check that if any of the child key columns in the above schema are NULL,
   868   1136   # there is no requirement for a corresponding parent key.
   869   1137   #