/ Check-in [39e32187]
Login

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

Overview
Comment:Many new test cases added, that mostly work. Currently 18 errors in without_rowid3.test. Also there is a hack marked by a /*FIXME*/ comment on at fkey.c:547 that needs fixing.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1:39e32187b66405e00dbd44685f6cdd269c90f5e5
User & Date: drh 2013-11-02 22:09:48
Context
2013-11-02
22:29
Improved Synopsis on register comparison operators. Fix a bug on the constraint check generator. check-in: a7a18b65 user: drh tags: omit-rowid
22:09
Many new test cases added, that mostly work. Currently 18 errors in without_rowid3.test. Also there is a hack marked by a /*FIXME*/ comment on at fkey.c:547 that needs fixing. check-in: 39e32187 user: drh tags: omit-rowid
19:34
Change the "idx" name of the primary key index for WITHOUT ROWID tables in sqlite_statN statistics tables to be the name of the table rather than the fabricated index name (ex: sqlite_autoindex_xyz_1). This makes it consistent with sqlite_master table. check-in: 4ee4d310 user: drh tags: omit-rowid
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  1649   1649       if( pList==0 ) return;
  1650   1650       pList->a[0].zName = sqlite3DbStrDup(pParse->db,
  1651   1651                                           pTab->aCol[pTab->iPKey].zName);
  1652   1652       pList->a[0].sortOrder = pParse->iPkSortOrder;
  1653   1653       assert( pParse->pNewTable==pTab );
  1654   1654       pPk = sqlite3CreateIndex(pParse, 0, 0, 0, pList, pTab->keyConf, 0, 0, 0, 0);
  1655   1655       if( pPk==0 ) return;
         1656  +    pPk->autoIndex = 2;
  1656   1657       pTab->iPKey = -1;
  1657   1658     }else{
  1658   1659       pPk = sqlite3PrimaryKeyIndex(pTab);
  1659   1660     }
  1660   1661     assert( pPk!=0 );
  1661   1662     nPk = pPk->nKeyCol;
  1662   1663   

Changes to src/delete.c.

   395    395       sqlite3VdbeAddOp2(v, OP_IdxInsert, iEph, iKey);
   396    396       sqlite3WhereEnd(pWInfo);
   397    397   
   398    398       /* Open cursors for all indices of the table.
   399    399       */
   400    400       sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite,
   401    401                                  iTabCur, &iDataCur, &iIdxCur);
   402         -    assert( iDataCur!=iTabCur );
   403         -    assert( iDataCur>=iIdxCur );
   404    402   
   405    403       /* Loop over the primary keys to be deleted. */
   406    404       addr = sqlite3VdbeAddOp1(v, OP_Rewind, iEph);
   407    405       sqlite3VdbeAddOp2(v, OP_RowKey, iEph, iPk);
   408    406   
   409    407       /* Delete the row */
   410    408       sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,

Changes to src/fkey.c.

   540    540       pWhere = sqlite3ExprAnd(db, pWhere, pEq);
   541    541     }
   542    542   
   543    543     /* If the child table is the same as the parent table, and this scan
   544    544     ** is taking place as part of a DELETE operation (operation D.2), omit the
   545    545     ** row being deleted from the scan by adding ($rowid != rowid) to the WHERE 
   546    546     ** clause, where $rowid is the rowid of the row being deleted.  */
   547         -  if( pTab==pFKey->pFrom && nIncr>0 ){
          547  +  if( pTab==pFKey->pFrom && nIncr>0 && HasRowid(pTab) /*FIXME*/ ){
   548    548       Expr *pEq;                    /* Expression (pLeft = pRight) */
   549    549       Expr *pLeft;                  /* Value from parent table row */
   550    550       Expr *pRight;                 /* Column ref to child table */
   551    551       pLeft = sqlite3Expr(db, TK_REGISTER, 0);
   552    552       pRight = sqlite3Expr(db, TK_COLUMN, 0);
   553    553       if( pLeft && pRight ){
   554    554         pLeft->iTable = regData;

Changes to src/vdbeblob.c.

   174    174   
   175    175       sqlite3BtreeEnterAll(db);
   176    176       pTab = sqlite3LocateTable(pParse, 0, zTable, zDb);
   177    177       if( pTab && IsVirtual(pTab) ){
   178    178         pTab = 0;
   179    179         sqlite3ErrorMsg(pParse, "cannot open virtual table: %s", zTable);
   180    180       }
          181  +    if( pTab && !HasRowid(pTab) ){
          182  +      pTab = 0;
          183  +      sqlite3ErrorMsg(pParse, "cannot open table without rowid: %s", zTable);
          184  +    }
   181    185   #ifndef SQLITE_OMIT_VIEW
   182    186       if( pTab && pTab->pSelect ){
   183    187         pTab = 0;
   184    188         sqlite3ErrorMsg(pParse, "cannot open view: %s", zTable);
   185    189       }
   186    190   #endif
   187    191       if( !pTab ){

Changes to test/without_rowid1.test.

    87     87   
    88     88   do_execsql_test without_rowid1-1.40 {
    89     89     VACUUM;
    90     90     SELECT *, '|' FROM t1 ORDER BY b, d;
    91     91   } {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
    92     92   integrity_check without_rowid1-1.41
    93     93   
           94  +# Verify that ANALYZE works
           95  +#
    94     96   do_execsql_test without_rowid1-1.50 {
    95     97     ANALYZE;
    96     98     SELECT * FROM sqlite_stat1 ORDER BY idx;
    97     99   } {t1 t1 {4 2 1} t1 t1bd {4 2 2}}
    98         -
          100  +ifcapable stat3 {
          101  +  do_execsql_test without_rowid1-1.51 {
          102  +    SELECT DISTINCT tbl, idx FROM sqlite_stat3 ORDER BY idx;
          103  +  } {t1 t1 t1 t1bd}
          104  +}
          105  +ifcapable stat4 {
          106  +  do_execsql_test without_rowid1-1.52 {
          107  +    SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx;
          108  +  } {t1 t1 t1 t1bd}
          109  +}
    99    110   
   100    111   finish_test

Added test/without_rowid2.test.

            1  +# 2013-11-02
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# This file implements regression tests for SQLite library.  The
           13  +# focus of this file is testing WITHOUT ROWID tables, and especially
           14  +# FOREIGN KEY constraints.
           15  +#
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +ifcapable {!foreignkey} {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +# Create a table and some data to work with.
           26  +#
           27  +do_test without_rowid2-1.0 {
           28  +  execsql {
           29  +    CREATE TABLE t1(
           30  +      a INT PRIMARY KEY,
           31  +      b INT
           32  +           REFERENCES t1 ON DELETE CASCADE
           33  +           REFERENCES t2,
           34  +      c TEXT,
           35  +      FOREIGN KEY (b,c) REFERENCES t2(x,y) ON UPDATE CASCADE
           36  +    ) WITHOUT rowid;
           37  +  }
           38  +} {}
           39  +do_test without_rowid2-1.1 {
           40  +  execsql {
           41  +    CREATE TABLE t2(
           42  +      x INT PRIMARY KEY,
           43  +      y TEXT
           44  +    ) WITHOUT rowid;
           45  +  }
           46  +} {}
           47  +do_test without_rowid2-1.2 {
           48  +  execsql {
           49  +    CREATE TABLE t3(
           50  +      a INT REFERENCES t2,
           51  +      b INT REFERENCES t1,
           52  +      FOREIGN KEY (a,b) REFERENCES t2(x,y)
           53  +    );
           54  +  }
           55  +} {}
           56  +
           57  +do_test without_rowid2-2.1 {
           58  +  execsql {
           59  +    CREATE TABLE t4(a int primary key) WITHOUT rowid;
           60  +    CREATE TABLE t5(x references t4);
           61  +    CREATE TABLE t6(x references t4);
           62  +    CREATE TABLE t7(x references t4);
           63  +    CREATE TABLE t8(x references t4);
           64  +    CREATE TABLE t9(x references t4);
           65  +    CREATE TABLE t10(x references t4);
           66  +    DROP TABLE t7;
           67  +    DROP TABLE t9;
           68  +    DROP TABLE t5;
           69  +    DROP TABLE t8;
           70  +    DROP TABLE t6;
           71  +    DROP TABLE t10;
           72  +  }
           73  +} {}
           74  +
           75  +do_test without_rowid2-3.1 {
           76  +  execsql {
           77  +    CREATE TABLE t5(a PRIMARY KEY, b, c) WITHOUT rowid;
           78  +    CREATE TABLE t6(
           79  +      d REFERENCES t5,
           80  +      e REFERENCES t5(c)
           81  +    );
           82  +    PRAGMA foreign_key_list(t6);
           83  +  }
           84  +} [concat                                         \
           85  +  {0 0 t5 e c {NO ACTION} {NO ACTION} NONE}       \
           86  +  {1 0 t5 d {} {NO ACTION} {NO ACTION} NONE}      \
           87  +]
           88  +do_test without_rowid2-3.2 {
           89  +  execsql {
           90  +    CREATE TABLE t7(d, e, f,
           91  +      FOREIGN KEY (d, e) REFERENCES t5(a, b)
           92  +    );
           93  +    PRAGMA foreign_key_list(t7);
           94  +  }
           95  +} [concat                                   \
           96  +  {0 0 t5 d a {NO ACTION} {NO ACTION} NONE} \
           97  +  {0 1 t5 e b {NO ACTION} {NO ACTION} NONE} \
           98  +]
           99  +do_test without_rowid2-3.3 {
          100  +  execsql {
          101  +    CREATE TABLE t8(d, e, f,
          102  +      FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL
          103  +    );
          104  +    PRAGMA foreign_key_list(t8);
          105  +  }
          106  +} [concat                        \
          107  +  {0 0 t5 d {} {SET NULL} CASCADE NONE} \
          108  +  {0 1 t5 e {} {SET NULL} CASCADE NONE} \
          109  +]
          110  +do_test without_rowid2-3.4 {
          111  +  execsql {
          112  +    CREATE TABLE t9(d, e, f,
          113  +      FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET DEFAULT
          114  +    );
          115  +    PRAGMA foreign_key_list(t9);
          116  +  }
          117  +} [concat                        \
          118  +  {0 0 t5 d {} {SET DEFAULT} CASCADE NONE} \
          119  +  {0 1 t5 e {} {SET DEFAULT} CASCADE NONE} \
          120  +]
          121  +do_test without_rowid2-3.5 {
          122  +  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
          123  +} {0 0 0}
          124  +
          125  +finish_test

Added test/without_rowid3.test.

            1  +# 2013-11-02
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +# This file implements tests for foreign keys on WITHOUT ROWID
           14  +# tables.
           15  +#
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +ifcapable {!foreignkey||!trigger} {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +#-------------------------------------------------------------------------
           26  +# Test structure:
           27  +#
           28  +# without_rowid3-1.*: Simple tests to check that immediate and deferred foreign key 
           29  +#            constraints work when not inside a transaction.
           30  +#            
           31  +# without_rowid3-2.*: Tests to verify that deferred foreign keys work inside
           32  +#            explicit transactions (i.e that processing really is deferred).
           33  +#
           34  +# without_rowid3-3.*: Tests that a statement transaction is rolled back if an
           35  +#            immediate foreign key constraint is violated.
           36  +#
           37  +# without_rowid3-4.*: Test that FK actions may recurse even when recursive triggers
           38  +#            are disabled.
           39  +#
           40  +# without_rowid3-5.*: Check that if foreign-keys are enabled, it is not possible
           41  +#            to write to an FK column using the incremental blob API.
           42  +#
           43  +# without_rowid3-6.*: Test that FK processing is automatically disabled when 
           44  +#            running VACUUM.
           45  +#
           46  +# without_rowid3-7.*: Test using an IPK as the key in the child (referencing) table.
           47  +#
           48  +# without_rowid3-8.*: Test that enabling/disabling foreign key support while a 
           49  +#            transaction is active is not possible.
           50  +#
           51  +# without_rowid3-9.*: Test SET DEFAULT actions.
           52  +#
           53  +# without_rowid3-10.*: Test errors.
           54  +#
           55  +# without_rowid3-11.*: Test CASCADE actions.
           56  +#
           57  +# without_rowid3-12.*: Test RESTRICT actions.
           58  +#
           59  +# without_rowid3-13.*: Test that FK processing is performed when a row is REPLACED by
           60  +#             an UPDATE or INSERT statement.
           61  +#
           62  +# without_rowid3-14.*: Test the ALTER TABLE and DROP TABLE commands.
           63  +#
           64  +# without_rowid3-15.*: Test that if there are no (known) outstanding foreign key 
           65  +#             constraint violations in the database, inserting into a parent
           66  +#             table or deleting from a child table does not cause SQLite
           67  +#             to check if this has repaired an outstanding violation.
           68  +#
           69  +# without_rowid3-16.*: Test that rows that refer to themselves may be inserted, 
           70  +#             updated and deleted.
           71  +#
           72  +# without_rowid3-17.*: Test that the "count_changes" pragma does not interfere with
           73  +#             FK constraint processing.
           74  +# 
           75  +# without_rowid3-18.*: Test that the authorization callback is invoked when processing
           76  +#             FK constraints.
           77  +#
           78  +# without_rowid3-20.*: Test that ON CONFLICT clauses specified as part of statements
           79  +#             do not affect the operation of FK constraints.
           80  +#
           81  +# without_rowid3-genfkey.*: Tests that were used with the shell tool .genfkey
           82  +#            command. Recycled to test the built-in implementation.
           83  +#
           84  +# without_rowid3-dd08e5.*:  Tests to verify that ticket dd08e5a988d00decc4a543daa8d
           85  +#                  has been fixed.
           86  +#
           87  +
           88  +
           89  +execsql { PRAGMA foreign_keys = on }
           90  +
           91  +set FkeySimpleSchema {
           92  +  PRAGMA foreign_keys = on;
           93  +  CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid;
           94  +  CREATE TABLE t2(c REFERENCES t1(a) /D/ , d);
           95  +
           96  +  CREATE TABLE t3(a PRIMARY KEY, b) WITHOUT rowid;
           97  +  CREATE TABLE t4(c REFERENCES t3 /D/, d);
           98  +
           99  +  CREATE TABLE t7(a, b INT PRIMARY KEY) WITHOUT rowid;
          100  +  CREATE TABLE t8(c REFERENCES t7 /D/, d);
          101  +
          102  +  CREATE TABLE t9(a REFERENCES nosuchtable, b);
          103  +  CREATE TABLE t10(a REFERENCES t9(c) /D/, b);
          104  +}
          105  +
          106  +
          107  +set FkeySimpleTests {
          108  +  1.1  "INSERT INTO t2 VALUES(1, 3)"      {1 {foreign key constraint failed}}
          109  +  1.2  "INSERT INTO t1 VALUES(1, 2)"      {0 {}}
          110  +  1.3  "INSERT INTO t2 VALUES(1, 3)"      {0 {}}
          111  +  1.4  "INSERT INTO t2 VALUES(2, 4)"      {1 {foreign key constraint failed}}
          112  +  1.5  "INSERT INTO t2 VALUES(NULL, 4)"   {0 {}}
          113  +  1.6  "UPDATE t2 SET c=2 WHERE d=4"      {1 {foreign key constraint failed}}
          114  +  1.7  "UPDATE t2 SET c=1 WHERE d=4"      {0 {}}
          115  +  1.9  "UPDATE t2 SET c=1 WHERE d=4"      {0 {}}
          116  +  1.10 "UPDATE t2 SET c=NULL WHERE d=4"   {0 {}}
          117  +  1.11 "DELETE FROM t1 WHERE a=1"         {1 {foreign key constraint failed}}
          118  +  1.12 "UPDATE t1 SET a = 2"              {1 {foreign key constraint failed}}
          119  +  1.13 "UPDATE t1 SET a = 1"              {0 {}}
          120  +
          121  +  2.1  "INSERT INTO t4 VALUES(1, 3)"      {1 {foreign key constraint failed}}
          122  +  2.2  "INSERT INTO t3 VALUES(1, 2)"      {0 {}}
          123  +  2.3  "INSERT INTO t4 VALUES(1, 3)"      {0 {}}
          124  +
          125  +  4.1  "INSERT INTO t8 VALUES(1, 3)"      {1 {foreign key constraint failed}}
          126  +  4.2  "INSERT INTO t7 VALUES(2, 1)"      {0 {}}
          127  +  4.3  "INSERT INTO t8 VALUES(1, 3)"      {0 {}}
          128  +  4.4  "INSERT INTO t8 VALUES(2, 4)"      {1 {foreign key constraint failed}}
          129  +  4.5  "INSERT INTO t8 VALUES(NULL, 4)"   {0 {}}
          130  +  4.6  "UPDATE t8 SET c=2 WHERE d=4"      {1 {foreign key constraint failed}}
          131  +  4.7  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
          132  +  4.9  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
          133  +  4.10 "UPDATE t8 SET c=NULL WHERE d=4"   {0 {}}
          134  +  4.11 "DELETE FROM t7 WHERE b=1"         {1 {foreign key constraint failed}}
          135  +  4.12 "UPDATE t7 SET b = 2"              {1 {foreign key constraint failed}}
          136  +  4.13 "UPDATE t7 SET b = 1"              {0 {}}
          137  +  4.14 "INSERT INTO t8 VALUES('a', 'b')"  {1 {foreign key constraint failed}}
          138  +  4.15 "UPDATE t7 SET b = 5"              {1 {foreign key constraint failed}}
          139  +  4.17 "UPDATE t7 SET a = 10"             {0 {}}
          140  +
          141  +  5.1  "INSERT INTO t9 VALUES(1, 3)"      {1 {no such table: main.nosuchtable}}
          142  +  5.2  "INSERT INTO t10 VALUES(1, 3)"  
          143  +                            {1 {foreign key mismatch - "t10" referencing "t9"}}
          144  +}
          145  +
          146  +do_test without_rowid3-1.1.0 {
          147  +  execsql [string map {/D/ {}} $FkeySimpleSchema]
          148  +} {}
          149  +foreach {tn zSql res} $FkeySimpleTests {
          150  +  do_test without_rowid3-1.1.$tn.1 { catchsql $zSql } $res
          151  +  do_test without_rowid3-1.1.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
          152  +  do_test without_rowid3-1.1.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
          153  +  do_test without_rowid3-1.1.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
          154  +  do_test without_rowid3-1.1.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
          155  +  do_test without_rowid3-1.1.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
          156  +  do_test without_rowid3-1.1.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
          157  +}
          158  +drop_all_tables
          159  +
          160  +do_test without_rowid3-1.2.0 {
          161  +  execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema]
          162  +} {}
          163  +foreach {tn zSql res} $FkeySimpleTests {
          164  +  do_test without_rowid3-1.2.$tn { catchsql $zSql } $res
          165  +  do_test without_rowid3-1.2.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
          166  +  do_test without_rowid3-1.2.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
          167  +  do_test without_rowid3-1.2.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
          168  +  do_test without_rowid3-1.2.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
          169  +  do_test without_rowid3-1.2.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
          170  +  do_test without_rowid3-1.2.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
          171  +}
          172  +drop_all_tables
          173  +
          174  +do_test without_rowid3-1.3.0 {
          175  +  execsql [string map {/D/ {}} $FkeySimpleSchema]
          176  +  execsql { PRAGMA count_changes = 1 }
          177  +} {}
          178  +foreach {tn zSql res} $FkeySimpleTests {
          179  +  if {$res == "0 {}"} { set res {0 1} }
          180  +  do_test without_rowid3-1.3.$tn { catchsql $zSql } $res
          181  +  do_test without_rowid3-1.3.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
          182  +  do_test without_rowid3-1.3.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
          183  +  do_test without_rowid3-1.3.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
          184  +  do_test without_rowid3-1.3.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
          185  +  do_test without_rowid3-1.3.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
          186  +  do_test without_rowid3-1.3.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
          187  +}
          188  +execsql { PRAGMA count_changes = 0 }
          189  +drop_all_tables
          190  +
          191  +do_test without_rowid3-1.4.0 {
          192  +  execsql [string map {/D/ {}} $FkeySimpleSchema]
          193  +  execsql { PRAGMA count_changes = 1 }
          194  +} {}
          195  +foreach {tn zSql res} $FkeySimpleTests {
          196  +  if {$res == "0 {}"} { set res {0 1} }
          197  +  execsql BEGIN
          198  +  do_test without_rowid3-1.4.$tn { catchsql $zSql } $res
          199  +  execsql COMMIT
          200  +}
          201  +execsql { PRAGMA count_changes = 0 }
          202  +drop_all_tables
          203  +
          204  +# Special test: When the parent key is an IPK, make sure the affinity of
          205  +# the IPK is not applied to the child key value before it is inserted
          206  +# into the child table.
          207  +do_test without_rowid3-1.5.1 {
          208  +  execsql {
          209  +    CREATE TABLE i(i INT PRIMARY KEY) WITHOUT rowid;
          210  +    CREATE TABLE j(j REFERENCES i);
          211  +    INSERT INTO i VALUES(35);
          212  +    INSERT INTO j VALUES('35.0');
          213  +    SELECT j, typeof(j) FROM j;
          214  +  }
          215  +} {35.0 text}
          216  +do_test without_rowid3-1.5.2 {
          217  +  catchsql { DELETE FROM i }
          218  +} {1 {foreign key constraint failed}}
          219  +
          220  +# Same test using a regular primary key with integer affinity.
          221  +drop_all_tables
          222  +do_test without_rowid3-1.6.1 {
          223  +  execsql {
          224  +    CREATE TABLE i(i INT UNIQUE);
          225  +    CREATE TABLE j(j REFERENCES i(i));
          226  +    INSERT INTO i VALUES('35.0');
          227  +    INSERT INTO j VALUES('35.0');
          228  +    SELECT j, typeof(j) FROM j;
          229  +    SELECT i, typeof(i) FROM i;
          230  +  }
          231  +} {35.0 text 35 integer}
          232  +do_test without_rowid3-1.6.2 {
          233  +  catchsql { DELETE FROM i }
          234  +} {1 {foreign key constraint failed}}
          235  +
          236  +# Use a collation sequence on the parent key.
          237  +drop_all_tables
          238  +do_test without_rowid3-1.7.1 {
          239  +  execsql {
          240  +    CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY) WITHOUT rowid;
          241  +    CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i));
          242  +    INSERT INTO i VALUES('SQLite');
          243  +    INSERT INTO j VALUES('sqlite');
          244  +  }
          245  +  catchsql { DELETE FROM i }
          246  +} {1 {foreign key constraint failed}}
          247  +
          248  +# Use the parent key collation even if it is default and the child key
          249  +# has an explicit value.
          250  +drop_all_tables
          251  +do_test without_rowid3-1.7.2 {
          252  +  execsql {
          253  +    CREATE TABLE i(i TEXT PRIMARY KEY) WITHOUT rowid;  -- Colseq is "BINARY"
          254  +    CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i));
          255  +    INSERT INTO i VALUES('SQLite');
          256  +  }
          257  +  catchsql { INSERT INTO j VALUES('sqlite') }
          258  +} {1 {foreign key constraint failed}}
          259  +do_test without_rowid3-1.7.3 {
          260  +  execsql {
          261  +    INSERT INTO i VALUES('sqlite');
          262  +    INSERT INTO j VALUES('sqlite');
          263  +    DELETE FROM i WHERE i = 'SQLite';
          264  +  }
          265  +  catchsql { DELETE FROM i WHERE i = 'sqlite' }
          266  +} {1 {foreign key constraint failed}}
          267  +
          268  +#-------------------------------------------------------------------------
          269  +# This section (test cases without_rowid3-2.*) contains tests to check that the
          270  +# deferred foreign key constraint logic works.
          271  +#
          272  +proc without_rowid3-2-test {tn nocommit sql {res {}}} {
          273  +  if {$res eq "FKV"} {
          274  +    set expected {1 {foreign key constraint failed}}
          275  +  } else {
          276  +    set expected [list 0 $res]
          277  +  }
          278  +  do_test without_rowid3-2.$tn [list catchsql $sql] $expected
          279  +  if {$nocommit} {
          280  +    do_test without_rowid3-2.${tn}c {
          281  +      catchsql COMMIT
          282  +    } {1 {foreign key constraint failed}}
          283  +  }
          284  +}
          285  +
          286  +without_rowid3-2-test 1 0 {
          287  +  CREATE TABLE node(
          288  +    nodeid PRIMARY KEY,
          289  +    parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
          290  +  ) WITHOUT rowid;
          291  +  CREATE TABLE leaf(
          292  +    cellid PRIMARY KEY,
          293  +    parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
          294  +  ) WITHOUT rowid;
          295  +}
          296  +
          297  +without_rowid3-2-test 1  0 "INSERT INTO node VALUES(1, 0)"       FKV
          298  +without_rowid3-2-test 2  0 "BEGIN"
          299  +without_rowid3-2-test 3  1   "INSERT INTO node VALUES(1, 0)"
          300  +without_rowid3-2-test 4  0   "UPDATE node SET parent = NULL"
          301  +without_rowid3-2-test 5  0 "COMMIT"
          302  +without_rowid3-2-test 6  0 "SELECT * FROM node" {1 {}}
          303  +
          304  +without_rowid3-2-test 7  0 "BEGIN"
          305  +without_rowid3-2-test 8  1   "INSERT INTO leaf VALUES('a', 2)"
          306  +without_rowid3-2-test 9  1   "INSERT INTO node VALUES(2, 0)"
          307  +without_rowid3-2-test 10 0   "UPDATE node SET parent = 1 WHERE nodeid = 2"
          308  +without_rowid3-2-test 11 0 "COMMIT"
          309  +without_rowid3-2-test 12 0 "SELECT * FROM node" {1 {} 2 1}
          310  +without_rowid3-2-test 13 0 "SELECT * FROM leaf" {a 2}
          311  +
          312  +without_rowid3-2-test 14 0 "BEGIN"
          313  +without_rowid3-2-test 15 1   "DELETE FROM node WHERE nodeid = 2"
          314  +without_rowid3-2-test 16 0   "INSERT INTO node VALUES(2, NULL)"
          315  +without_rowid3-2-test 17 0 "COMMIT"
          316  +without_rowid3-2-test 18 0 "SELECT * FROM node" {1 {} 2 {}}
          317  +without_rowid3-2-test 19 0 "SELECT * FROM leaf" {a 2}
          318  +
          319  +without_rowid3-2-test 20 0 "BEGIN"
          320  +without_rowid3-2-test 21 0   "INSERT INTO leaf VALUES('b', 1)"
          321  +without_rowid3-2-test 22 0   "SAVEPOINT save"
          322  +without_rowid3-2-test 23 0     "DELETE FROM node WHERE nodeid = 1"
          323  +without_rowid3-2-test 24 0   "ROLLBACK TO save"
          324  +without_rowid3-2-test 25 0 "COMMIT"
          325  +without_rowid3-2-test 26 0 "SELECT * FROM node" {1 {} 2 {}}
          326  +without_rowid3-2-test 27 0 "SELECT * FROM leaf" {a 2 b 1}
          327  +
          328  +without_rowid3-2-test 28 0 "BEGIN"
          329  +without_rowid3-2-test 29 0   "INSERT INTO leaf VALUES('c', 1)"
          330  +without_rowid3-2-test 30 0   "SAVEPOINT save"
          331  +without_rowid3-2-test 31 0     "DELETE FROM node WHERE nodeid = 1"
          332  +without_rowid3-2-test 32 1   "RELEASE save"
          333  +without_rowid3-2-test 33 1   "DELETE FROM leaf WHERE cellid = 'b'"
          334  +without_rowid3-2-test 34 0   "DELETE FROM leaf WHERE cellid = 'c'"
          335  +without_rowid3-2-test 35 0 "COMMIT"
          336  +without_rowid3-2-test 36 0 "SELECT * FROM node" {2 {}} 
          337  +without_rowid3-2-test 37 0 "SELECT * FROM leaf" {a 2}
          338  +
          339  +without_rowid3-2-test 38 0 "SAVEPOINT outer"
          340  +without_rowid3-2-test 39 1   "INSERT INTO leaf VALUES('d', 3)"
          341  +without_rowid3-2-test 40 1 "RELEASE outer"    FKV
          342  +without_rowid3-2-test 41 1   "INSERT INTO leaf VALUES('e', 3)"
          343  +without_rowid3-2-test 42 0   "INSERT INTO node VALUES(3, 2)"
          344  +without_rowid3-2-test 43 0 "RELEASE outer"
          345  +
          346  +without_rowid3-2-test 44 0 "SAVEPOINT outer"
          347  +without_rowid3-2-test 45 1   "DELETE FROM node WHERE nodeid=3"
          348  +without_rowid3-2-test 47 0   "INSERT INTO node VALUES(3, 2)"
          349  +without_rowid3-2-test 48 0 "ROLLBACK TO outer"
          350  +without_rowid3-2-test 49 0 "RELEASE outer"
          351  +
          352  +without_rowid3-2-test 50 0 "SAVEPOINT outer"
          353  +without_rowid3-2-test 51 1   "INSERT INTO leaf VALUES('f', 4)"
          354  +without_rowid3-2-test 52 1   "SAVEPOINT inner"
          355  +without_rowid3-2-test 53 1     "INSERT INTO leaf VALUES('g', 4)"
          356  +without_rowid3-2-test 54 1  "RELEASE outer"   FKV
          357  +without_rowid3-2-test 55 1   "ROLLBACK TO inner"
          358  +without_rowid3-2-test 56 0  "COMMIT"          FKV
          359  +without_rowid3-2-test 57 0   "INSERT INTO node VALUES(4, NULL)"
          360  +without_rowid3-2-test 58 0 "RELEASE outer"
          361  +without_rowid3-2-test 59 0 "SELECT * FROM node" {2 {} 3 2 4 {}}
          362  +without_rowid3-2-test 60 0 "SELECT * FROM leaf" {a 2 d 3 e 3 f 4}
          363  +
          364  +# The following set of tests check that if a statement that affects 
          365  +# multiple rows violates some foreign key constraints, then strikes a 
          366  +# constraint that causes the statement-transaction to be rolled back, 
          367  +# the deferred constraint counter is correctly reset to the value it 
          368  +# had before the statement-transaction was opened.
          369  +#
          370  +without_rowid3-2-test 61 0 "BEGIN"
          371  +without_rowid3-2-test 62 0   "DELETE FROM leaf"
          372  +without_rowid3-2-test 63 0   "DELETE FROM node"
          373  +without_rowid3-2-test 64 1   "INSERT INTO leaf VALUES('a', 1)"
          374  +without_rowid3-2-test 65 1   "INSERT INTO leaf VALUES('b', 2)"
          375  +without_rowid3-2-test 66 1   "INSERT INTO leaf VALUES('c', 1)"
          376  +do_test without_rowid3-2-test-67 {
          377  +  catchsql          "INSERT INTO node SELECT parent, 3 FROM leaf"
          378  +} {1 {column nodeid is not unique}}
          379  +without_rowid3-2-test 68 0 "COMMIT"           FKV
          380  +without_rowid3-2-test 69 1   "INSERT INTO node VALUES(1, NULL)"
          381  +without_rowid3-2-test 70 0   "INSERT INTO node VALUES(2, NULL)"
          382  +without_rowid3-2-test 71 0 "COMMIT"
          383  +
          384  +without_rowid3-2-test 72 0 "BEGIN"
          385  +without_rowid3-2-test 73 1   "DELETE FROM node"
          386  +without_rowid3-2-test 74 0   "INSERT INTO node(nodeid) SELECT DISTINCT parent FROM leaf"
          387  +without_rowid3-2-test 75 0 "COMMIT"
          388  +
          389  +#-------------------------------------------------------------------------
          390  +# Test cases without_rowid3-3.* test that a program that executes foreign key
          391  +# actions (CASCADE, SET DEFAULT, SET NULL etc.) or tests FK constraints
          392  +# opens a statement transaction if required.
          393  +#
          394  +# without_rowid3-3.1.*: Test UPDATE statements.
          395  +# without_rowid3-3.2.*: Test DELETE statements.
          396  +#
          397  +drop_all_tables
          398  +do_test without_rowid3-3.1.1 {
          399  +  execsql {
          400  +    CREATE TABLE ab(a PRIMARY KEY, b) WITHOUT rowid;
          401  +    CREATE TABLE cd(
          402  +      c PRIMARY KEY REFERENCES ab ON UPDATE CASCADE ON DELETE CASCADE, 
          403  +      d
          404  +    ) WITHOUT rowid;
          405  +    CREATE TABLE ef(
          406  +      e REFERENCES cd ON UPDATE CASCADE, 
          407  +      f, CHECK (e!=5)
          408  +    );
          409  +  }
          410  +} {}
          411  +do_test without_rowid3-3.1.2 {
          412  +  execsql {
          413  +    INSERT INTO ab VALUES(1, 'b');
          414  +    INSERT INTO cd VALUES(1, 'd');
          415  +    INSERT INTO ef VALUES(1, 'e');
          416  +  }
          417  +} {}
          418  +do_test without_rowid3-3.1.3 {
          419  +  catchsql { UPDATE ab SET a = 5 }
          420  +} {1 {constraint failed}}
          421  +do_test without_rowid3-3.1.4 {
          422  +  execsql { SELECT * FROM ab }
          423  +} {1 b}
          424  +do_test without_rowid3-3.1.4 {
          425  +  execsql BEGIN;
          426  +  catchsql { UPDATE ab SET a = 5 }
          427  +} {1 {constraint failed}}
          428  +do_test without_rowid3-3.1.5 {
          429  +  execsql COMMIT;
          430  +  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
          431  +} {1 b 1 d 1 e}
          432  +
          433  +do_test without_rowid3-3.2.1 {
          434  +  execsql BEGIN;
          435  +  catchsql { DELETE FROM ab }
          436  +} {1 {foreign key constraint failed}}
          437  +do_test without_rowid3-3.2.2 {
          438  +  execsql COMMIT
          439  +  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
          440  +} {1 b 1 d 1 e}
          441  +
          442  +#-------------------------------------------------------------------------
          443  +# Test cases without_rowid3-4.* test that recursive foreign key actions 
          444  +# (i.e. CASCADE) are allowed even if recursive triggers are disabled.
          445  +#
          446  +drop_all_tables
          447  +do_test without_rowid3-4.1 {
          448  +  execsql {
          449  +    CREATE TABLE t1(
          450  +      node PRIMARY KEY, 
          451  +      parent REFERENCES t1 ON DELETE CASCADE
          452  +    ) WITHOUT rowid;
          453  +    CREATE TABLE t2(node PRIMARY KEY, parent) WITHOUT rowid;
          454  +    CREATE TRIGGER t2t AFTER DELETE ON t2 BEGIN
          455  +      DELETE FROM t2 WHERE parent = old.node;
          456  +    END;
          457  +    INSERT INTO t1 VALUES(1, NULL);
          458  +    INSERT INTO t1 VALUES(2, 1);
          459  +    INSERT INTO t1 VALUES(3, 1);
          460  +    INSERT INTO t1 VALUES(4, 2);
          461  +    INSERT INTO t1 VALUES(5, 2);
          462  +    INSERT INTO t1 VALUES(6, 3);
          463  +    INSERT INTO t1 VALUES(7, 3);
          464  +    INSERT INTO t2 SELECT * FROM t1;
          465  +  }
          466  +} {}
          467  +do_test without_rowid3-4.2 {
          468  +  execsql { PRAGMA recursive_triggers = off }
          469  +  execsql { 
          470  +    BEGIN;
          471  +      DELETE FROM t1 WHERE node = 1;
          472  +      SELECT node FROM t1;
          473  +  }
          474  +} {}
          475  +do_test without_rowid3-4.3 {
          476  +  execsql { 
          477  +      DELETE FROM t2 WHERE node = 1;
          478  +      SELECT node FROM t2;
          479  +    ROLLBACK;
          480  +  }
          481  +} {4 5 6 7}
          482  +do_test without_rowid3-4.4 {
          483  +  execsql { PRAGMA recursive_triggers = on }
          484  +  execsql { 
          485  +    BEGIN;
          486  +      DELETE FROM t1 WHERE node = 1;
          487  +      SELECT node FROM t1;
          488  +  }
          489  +} {}
          490  +do_test without_rowid3-4.3 {
          491  +  execsql { 
          492  +      DELETE FROM t2 WHERE node = 1;
          493  +      SELECT node FROM t2;
          494  +    ROLLBACK;
          495  +  }
          496  +} {}
          497  +
          498  +#-------------------------------------------------------------------------
          499  +# Test cases without_rowid3-5.* verify that the incremental blob API may not
          500  +# write to a foreign key column while foreign-keys are enabled.
          501  +#
          502  +drop_all_tables
          503  +ifcapable incrblob {
          504  +  do_test without_rowid3-5.1 {
          505  +    execsql {
          506  +      CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid;
          507  +      CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1(a)) WITHOUT rowid;
          508  +      INSERT INTO t1 VALUES('hello', 'world');
          509  +      INSERT INTO t2 VALUES('key', 'hello');
          510  +    }
          511  +  } {}
          512  +  do_test without_rowid3-5.2 {
          513  +    set rc [catch { set fd [db incrblob t2 b 1] } msg]
          514  +    list $rc $msg
          515  +  } {1 {cannot open table without rowid: t2}}
          516  +  do_test without_rowid3-5.5 {
          517  +    execsql { PRAGMA foreign_keys = on }
          518  +  } {}
          519  +}
          520  +
          521  +drop_all_tables
          522  +ifcapable vacuum {
          523  +  do_test without_rowid3-6.1 {
          524  +    execsql {
          525  +      CREATE TABLE t1(a REFERENCES t2(c), b);
          526  +      CREATE TABLE t2(c UNIQUE, b);
          527  +      INSERT INTO t2 VALUES(1, 2);
          528  +      INSERT INTO t1 VALUES(1, 2);
          529  +      VACUUM;
          530  +    }
          531  +  } {}
          532  +}
          533  +
          534  +#-------------------------------------------------------------------------
          535  +# Test that it is possible to use an INT PRIMARY KEY as the child key
          536  +# of a foreign constraint.
          537  +# 
          538  +drop_all_tables
          539  +do_test without_rowid3-7.1 {
          540  +  execsql {
          541  +    CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid;
          542  +    CREATE TABLE t2(c INT PRIMARY KEY REFERENCES t1, b) WITHOUT rowid;
          543  +  }
          544  +} {}
          545  +do_test without_rowid3-7.2 {
          546  +  catchsql { INSERT INTO t2 VALUES(1, 'A'); }
          547  +} {1 {foreign key constraint failed}}
          548  +do_test without_rowid3-7.3 {
          549  +  execsql { 
          550  +    INSERT INTO t1 VALUES(1, 2);
          551  +    INSERT INTO t1 VALUES(2, 3);
          552  +    INSERT INTO t2 VALUES(1, 'A');
          553  +  }
          554  +} {}
          555  +do_test without_rowid3-7.4 {
          556  +  execsql { UPDATE t2 SET c = 2 }
          557  +} {}
          558  +do_test without_rowid3-7.5 {
          559  +  catchsql { UPDATE t2 SET c = 3 }
          560  +} {1 {foreign key constraint failed}}
          561  +do_test without_rowid3-7.6 {
          562  +  catchsql { DELETE FROM t1 WHERE a = 2 }
          563  +} {1 {foreign key constraint failed}}
          564  +do_test without_rowid3-7.7 {
          565  +  execsql { DELETE FROM t1 WHERE a = 1 }
          566  +} {}
          567  +do_test without_rowid3-7.8 {
          568  +  catchsql { UPDATE t1 SET a = 3 }
          569  +} {1 {foreign key constraint failed}}
          570  +
          571  +#-------------------------------------------------------------------------
          572  +# Test that it is not possible to enable/disable FK support while a
          573  +# transaction is open.
          574  +# 
          575  +drop_all_tables
          576  +proc without_rowid3-8-test {tn zSql value} {
          577  +  do_test without_rowid3-2.8.$tn.1 [list execsql $zSql] {}
          578  +  do_test without_rowid3-2.8.$tn.2 { execsql "PRAGMA foreign_keys" } $value
          579  +}
          580  +without_rowid3-8-test  1 { PRAGMA foreign_keys = 0     } 0
          581  +without_rowid3-8-test  2 { PRAGMA foreign_keys = 1     } 1
          582  +without_rowid3-8-test  3 { BEGIN                       } 1
          583  +without_rowid3-8-test  4 { PRAGMA foreign_keys = 0     } 1
          584  +without_rowid3-8-test  5 { COMMIT                      } 1
          585  +without_rowid3-8-test  6 { PRAGMA foreign_keys = 0     } 0
          586  +without_rowid3-8-test  7 { BEGIN                       } 0
          587  +without_rowid3-8-test  8 { PRAGMA foreign_keys = 1     } 0
          588  +without_rowid3-8-test  9 { COMMIT                      } 0
          589  +without_rowid3-8-test 10 { PRAGMA foreign_keys = 1     } 1
          590  +without_rowid3-8-test 11 { PRAGMA foreign_keys = off   } 0
          591  +without_rowid3-8-test 12 { PRAGMA foreign_keys = on    } 1
          592  +without_rowid3-8-test 13 { PRAGMA foreign_keys = no    } 0
          593  +without_rowid3-8-test 14 { PRAGMA foreign_keys = yes   } 1
          594  +without_rowid3-8-test 15 { PRAGMA foreign_keys = false } 0
          595  +without_rowid3-8-test 16 { PRAGMA foreign_keys = true  } 1
          596  +
          597  +#-------------------------------------------------------------------------
          598  +# The following tests, without_rowid3-9.*, test SET DEFAULT actions.
          599  +#
          600  +drop_all_tables
          601  +do_test without_rowid3-9.1.1 {
          602  +  execsql {
          603  +    CREATE TABLE t1(a INT PRIMARY KEY, b) WITHOUT rowid;
          604  +    CREATE TABLE t2(
          605  +      c INT PRIMARY KEY,
          606  +      d INTEGER DEFAULT 1 REFERENCES t1 ON DELETE SET DEFAULT
          607  +    ) WITHOUT rowid;
          608  +    DELETE FROM t1;
          609  +  }
          610  +} {}
          611  +do_test without_rowid3-9.1.2 {
          612  +  execsql {
          613  +    INSERT INTO t1 VALUES(1, 'one');
          614  +    INSERT INTO t1 VALUES(2, 'two');
          615  +    INSERT INTO t2 VALUES(1, 2);
          616  +    SELECT * FROM t2;
          617  +    DELETE FROM t1 WHERE a = 2;
          618  +    SELECT * FROM t2;
          619  +  }
          620  +} {1 2 1 1}
          621  +do_test without_rowid3-9.1.3 {
          622  +  execsql {
          623  +    INSERT INTO t1 VALUES(2, 'two');
          624  +    UPDATE t2 SET d = 2;
          625  +    DELETE FROM t1 WHERE a = 1;
          626  +    SELECT * FROM t2;
          627  +  }
          628  +} {1 2}
          629  +do_test without_rowid3-9.1.4 {
          630  +  execsql { SELECT * FROM t1 }
          631  +} {2 two}
          632  +do_test without_rowid3-9.1.5 {
          633  +  catchsql { DELETE FROM t1 }
          634  +} {1 {foreign key constraint failed}}
          635  +
          636  +do_test without_rowid3-9.2.1 {
          637  +  execsql {
          638  +    CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c)) WITHOUT rowid;
          639  +    CREATE TABLE cc(d DEFAULT 3, e DEFAULT 1, f DEFAULT 2,
          640  +        FOREIGN KEY(f, d) REFERENCES pp 
          641  +        ON UPDATE SET DEFAULT 
          642  +        ON DELETE SET NULL
          643  +    );
          644  +    INSERT INTO pp VALUES(1, 2, 3);
          645  +    INSERT INTO pp VALUES(4, 5, 6);
          646  +    INSERT INTO pp VALUES(7, 8, 9);
          647  +  }
          648  +} {}
          649  +do_test without_rowid3-9.2.2 {
          650  +  execsql {
          651  +    INSERT INTO cc VALUES(6, 'A', 5);
          652  +    INSERT INTO cc VALUES(6, 'B', 5);
          653  +    INSERT INTO cc VALUES(9, 'A', 8);
          654  +    INSERT INTO cc VALUES(9, 'B', 8);
          655  +    UPDATE pp SET b = 1 WHERE a = 7;
          656  +    SELECT * FROM cc;
          657  +  }
          658  +} {6 A 5 6 B 5 3 A 2 3 B 2}
          659  +do_test without_rowid3-9.2.3 {
          660  +  execsql {
          661  +    DELETE FROM pp WHERE a = 4;
          662  +    SELECT * FROM cc;
          663  +  }
          664  +} {{} A {} {} B {} 3 A 2 3 B 2}
          665  +
          666  +#-------------------------------------------------------------------------
          667  +# The following tests, without_rowid3-10.*, test "foreign key mismatch" and 
          668  +# other errors.
          669  +#
          670  +set tn 0
          671  +foreach zSql [list {
          672  +  CREATE TABLE p(a PRIMARY KEY, b) WITHOUT rowid;
          673  +  CREATE TABLE c(x REFERENCES p(c));
          674  +} {
          675  +  CREATE TABLE c(x REFERENCES v(y));
          676  +  CREATE VIEW v AS SELECT x AS y FROM c;
          677  +} {
          678  +  CREATE TABLE p(a, b, PRIMARY KEY(a, b)) WITHOUT rowid;
          679  +  CREATE TABLE c(x REFERENCES p);
          680  +} {
          681  +  CREATE TABLE p(a COLLATE binary, b);
          682  +  CREATE UNIQUE INDEX i ON p(a COLLATE nocase);
          683  +  CREATE TABLE c(x REFERENCES p(a));
          684  +}] {
          685  +  drop_all_tables
          686  +  do_test without_rowid3-10.1.[incr tn] {
          687  +    execsql $zSql
          688  +    catchsql { INSERT INTO c DEFAULT VALUES }
          689  +  } {/1 {foreign key mismatch - "c" referencing "."}/}
          690  +}
          691  +
          692  +# "rowid" cannot be used as part of a child or parent key definition 
          693  +# unless it happens to be the name of an explicitly declared column.
          694  +#
          695  +do_test without_rowid3-10.2.1 {
          696  +  drop_all_tables
          697  +  catchsql {
          698  +    CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid;
          699  +    CREATE TABLE t2(c, d, FOREIGN KEY(rowid) REFERENCES t1(a));
          700  +  }
          701  +} {1 {unknown column "rowid" in foreign key definition}}
          702  +do_test without_rowid3-10.2.2 {
          703  +  drop_all_tables
          704  +  catchsql {
          705  +    CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid;
          706  +    CREATE TABLE t2(rowid, d, FOREIGN KEY(rowid) REFERENCES t1(a));
          707  +  }
          708  +} {0 {}}
          709  +do_test without_rowid3-10.2.1 {
          710  +  drop_all_tables
          711  +  catchsql {
          712  +    CREATE TABLE t1(a, b);
          713  +    CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
          714  +    INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1);
          715  +    INSERT INTO t2 VALUES(1, 1);
          716  +  }
          717  +} {1 {foreign key mismatch - "t2" referencing "t1"}}
          718  +do_test without_rowid3-10.2.2 {
          719  +  drop_all_tables
          720  +  catchsql {
          721  +    CREATE TABLE t1(rowid PRIMARY KEY, b) WITHOUT rowid;
          722  +    CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
          723  +    INSERT INTO t1(rowid, b) VALUES(1, 1);
          724  +    INSERT INTO t2 VALUES(1, 1);
          725  +  }
          726  +} {0 {}}
          727  +
          728  +
          729  +#-------------------------------------------------------------------------
          730  +# The following tests, without_rowid3-11.*, test CASCADE actions.
          731  +#
          732  +drop_all_tables
          733  +do_test without_rowid3-11.1.1 {
          734  +  execsql {
          735  +    CREATE TABLE t1(a INT PRIMARY KEY, b) WITHOUT rowid;
          736  +    CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
          737  +
          738  +    INSERT INTO t1 VALUES(10, 100);
          739  +    INSERT INTO t2 VALUES(10, 100);
          740  +    UPDATE t1 SET a = 15;
          741  +    SELECT * FROM t2;
          742  +  }
          743  +} {15 100}
          744  +
          745  +#-------------------------------------------------------------------------
          746  +# The following tests, without_rowid3-12.*, test RESTRICT actions.
          747  +#
          748  +drop_all_tables
          749  +do_test without_rowid3-12.1.1 {
          750  +  execsql {
          751  +    CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT rowid;
          752  +    CREATE TABLE t2(
          753  +      x REFERENCES t1 ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED 
          754  +    );
          755  +    INSERT INTO t1 VALUES(1, 'one');
          756  +    INSERT INTO t1 VALUES(2, 'two');
          757  +    INSERT INTO t1 VALUES(3, 'three');
          758  +  }
          759  +} {}
          760  +do_test without_rowid3-12.1.2 { 
          761  +  execsql "BEGIN"
          762  +  execsql "INSERT INTO t2 VALUES('two')"
          763  +} {}
          764  +do_test without_rowid3-12.1.3 { 
          765  +  execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'"
          766  +} {}
          767  +do_test without_rowid3-12.1.4 { 
          768  +  catchsql "UPDATE t1 SET b = 'five' WHERE b = 'two'"
          769  +} {1 {foreign key constraint failed}}
          770  +do_test without_rowid3-12.1.5 { 
          771  +  execsql "DELETE FROM t1 WHERE b = 'two'"
          772  +} {}
          773  +do_test without_rowid3-12.1.6 { 
          774  +  catchsql "COMMIT"
          775  +} {1 {foreign key constraint failed}}
          776  +do_test without_rowid3-12.1.7 { 
          777  +  execsql {
          778  +    INSERT INTO t1 VALUES(2, 'two');
          779  +    COMMIT;
          780  +  }
          781  +} {}
          782  +
          783  +drop_all_tables
          784  +do_test without_rowid3-12.2.1 {
          785  +  execsql {
          786  +    CREATE TABLE t1(x COLLATE NOCASE PRIMARY KEY) WITHOUT rowid;
          787  +    CREATE TRIGGER tt1 AFTER DELETE ON t1 
          788  +      WHEN EXISTS ( SELECT 1 FROM t2 WHERE old.x = y )
          789  +    BEGIN
          790  +      INSERT INTO t1 VALUES(old.x);
          791  +    END;
          792  +    CREATE TABLE t2(y REFERENCES t1);
          793  +    INSERT INTO t1 VALUES('A');
          794  +    INSERT INTO t1 VALUES('B');
          795  +    INSERT INTO t2 VALUES('a');
          796  +    INSERT INTO t2 VALUES('b');
          797  +
          798  +    SELECT * FROM t1;
          799  +    SELECT * FROM t2;
          800  +  }
          801  +} {A B a b}
          802  +do_test without_rowid3-12.2.2 {
          803  +  execsql { DELETE FROM t1 }
          804  +  execsql {
          805  +    SELECT * FROM t1;
          806  +    SELECT * FROM t2;
          807  +  }
          808  +} {A B a b}
          809  +do_test without_rowid3-12.2.3 {
          810  +  execsql {
          811  +    DROP TABLE t2;
          812  +    CREATE TABLE t2(y REFERENCES t1 ON DELETE RESTRICT);
          813  +    INSERT INTO t2 VALUES('a');
          814  +    INSERT INTO t2 VALUES('b');
          815  +  }
          816  +  catchsql { DELETE FROM t1 }
          817  +} {1 {foreign key constraint failed}}
          818  +do_test without_rowid3-12.2.4 {
          819  +  execsql {
          820  +    SELECT * FROM t1;
          821  +    SELECT * FROM t2;
          822  +  }
          823  +} {A B a b}
          824  +
          825  +drop_all_tables
          826  +do_test without_rowid3-12.3.1 {
          827  +  execsql {
          828  +    CREATE TABLE up(
          829  +      c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
          830  +      c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
          831  +      c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
          832  +      c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
          833  +      PRIMARY KEY(c34, c35)
          834  +    ) WITHOUT rowid;
          835  +    CREATE TABLE down(
          836  +      c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
          837  +      c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
          838  +      c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
          839  +      c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
          840  +      FOREIGN KEY(c39, c38) REFERENCES up ON UPDATE CASCADE
          841  +    );
          842  +  }
          843  +} {}
          844  +do_test without_rowid3-12.3.2 {
          845  +  execsql {
          846  +    INSERT INTO up(c34, c35) VALUES('yes', 'no');
          847  +    INSERT INTO down(c39, c38) VALUES('yes', 'no');
          848  +    UPDATE up SET c34 = 'possibly';
          849  +    SELECT c38, c39 FROM down;
          850  +    DELETE FROM down;
          851  +  }
          852  +} {no possibly}
          853  +do_test without_rowid3-12.3.3 {
          854  +  catchsql { INSERT INTO down(c39, c38) VALUES('yes', 'no') }
          855  +} {1 {foreign key constraint failed}}
          856  +do_test without_rowid3-12.3.4 {
          857  +  execsql { 
          858  +    INSERT INTO up(c34, c35) VALUES('yes', 'no');
          859  +    INSERT INTO down(c39, c38) VALUES('yes', 'no');
          860  +  }
          861  +  catchsql { DELETE FROM up WHERE c34 = 'yes' }
          862  +} {1 {foreign key constraint failed}}
          863  +do_test without_rowid3-12.3.5 {
          864  +  execsql { 
          865  +    DELETE FROM up WHERE c34 = 'possibly';
          866  +    SELECT c34, c35 FROM up;
          867  +    SELECT c39, c38 FROM down;
          868  +  }
          869  +} {yes no yes no}
          870  +
          871  +#-------------------------------------------------------------------------
          872  +# The following tests, without_rowid3-13.*, test that FK processing is performed
          873  +# when rows are REPLACEd.
          874  +#
          875  +drop_all_tables
          876  +do_test without_rowid3-13.1.1 {
          877  +  execsql {
          878  +    CREATE TABLE pp(a UNIQUE, b, c, PRIMARY KEY(b, c)) WITHOUT rowid;
          879  +    CREATE TABLE cc(d, e, f UNIQUE, FOREIGN KEY(d, e) REFERENCES pp);
          880  +    INSERT INTO pp VALUES(1, 2, 3);
          881  +    INSERT INTO cc VALUES(2, 3, 1);
          882  +  }
          883  +} {}
          884  +foreach {tn stmt} {
          885  +  1   "REPLACE INTO pp VALUES(1, 4, 5)"
          886  +} {
          887  +  do_test without_rowid3-13.1.$tn.1 {
          888  +    catchsql $stmt
          889  +  } {1 {foreign key constraint failed}}
          890  +  do_test without_rowid3-13.1.$tn.2 {
          891  +    execsql {
          892  +      SELECT * FROM pp;
          893  +      SELECT * FROM cc;
          894  +    }
          895  +  } {1 2 3 2 3 1}
          896  +  do_test without_rowid3-13.1.$tn.3 {
          897  +    execsql BEGIN;
          898  +    catchsql $stmt
          899  +  } {1 {foreign key constraint failed}}
          900  +  do_test without_rowid3-13.1.$tn.4 {
          901  +    execsql {
          902  +      COMMIT;
          903  +      SELECT * FROM pp;
          904  +      SELECT * FROM cc;
          905  +    }
          906  +  } {1 2 3 2 3 1}
          907  +}
          908  +
          909  +#-------------------------------------------------------------------------
          910  +# The following tests, without_rowid3-14.*, test that the "DROP TABLE" and "ALTER
          911  +# TABLE" commands work as expected wrt foreign key constraints.
          912  +#
          913  +# without_rowid3-14.1*: ALTER TABLE ADD COLUMN
          914  +# without_rowid3-14.2*: ALTER TABLE RENAME TABLE
          915  +# without_rowid3-14.3*: DROP TABLE
          916  +#
          917  +drop_all_tables
          918  +ifcapable altertable {
          919  +  do_test without_rowid3-14.1.1 {
          920  +    # Adding a column with a REFERENCES clause is not supported.
          921  +    execsql { 
          922  +      CREATE TABLE t1(a PRIMARY KEY) WITHOUT rowid;
          923  +      CREATE TABLE t2(a, b);
          924  +    }
          925  +    catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
          926  +  } {0 {}}
          927  +  do_test without_rowid3-14.1.2 {
          928  +    catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
          929  +  } {0 {}}
          930  +  do_test without_rowid3-14.1.3 {
          931  +    catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
          932  +  } {0 {}}
          933  +  do_test without_rowid3-14.1.4 {
          934  +    catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
          935  +  } {1 {Cannot add a REFERENCES column with non-NULL default value}}
          936  +  do_test without_rowid3-14.1.5 {
          937  +    catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
          938  +  } {1 {Cannot add a REFERENCES column with non-NULL default value}}
          939  +  do_test without_rowid3-14.1.6 {
          940  +    execsql { 
          941  +      PRAGMA foreign_keys = off;
          942  +      ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
          943  +      PRAGMA foreign_keys = on;
          944  +      SELECT sql FROM sqlite_master WHERE name='t2';
          945  +    }
          946  +  } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
          947  +  
          948  +  
          949  +  # Test the sqlite_rename_parent() function directly.
          950  +  #
          951  +  proc test_rename_parent {zCreate zOld zNew} {
          952  +    db eval {SELECT sqlite_rename_parent($zCreate, $zOld, $zNew)}
          953  +  }
          954  +  do_test without_rowid3-14.2.1.1 {
          955  +    test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
          956  +  } {{CREATE TABLE t1(a REFERENCES "t3")}}
          957  +  do_test without_rowid3-14.2.1.2 {
          958  +    test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
          959  +  } {{CREATE TABLE t1(a REFERENCES t2)}}
          960  +  do_test without_rowid3-14.2.1.3 {
          961  +    test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
          962  +  } {{CREATE TABLE t1(a REFERENCES "t3")}}
          963  +  
          964  +  # Test ALTER TABLE RENAME TABLE a bit.
          965  +  #
          966  +  do_test without_rowid3-14.2.2.1 {
          967  +    drop_all_tables
          968  +    execsql {
          969  +      CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid;
          970  +      CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)
          971  +            WITHOUT rowid;
          972  +      CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
          973  +    }
          974  +    execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
          975  +  } [list \
          976  +    {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid}       \
          977  +    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)
          978  +            WITHOUT rowid}    \
          979  +    {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)}  \
          980  +  ]
          981  +  do_test without_rowid3-14.2.2.2 {
          982  +    execsql { ALTER TABLE t1 RENAME TO t4 }
          983  +    execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
          984  +  } [list \
          985  +    {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4") WITHOUT rowid}      \
          986  +    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)
          987  +            WITHOUT rowid}     \
          988  +    {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
          989  +  ]
          990  +  do_test without_rowid3-14.2.2.3 {
          991  +    catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
          992  +  } {1 {foreign key constraint failed}}
          993  +  do_test without_rowid3-14.2.2.4 {
          994  +    execsql { INSERT INTO t4 VALUES(1, NULL) }
          995  +  } {}
          996  +  do_test without_rowid3-14.2.2.5 {
          997  +    catchsql { UPDATE t4 SET b = 5 }
          998  +  } {1 {foreign key constraint failed}}
          999  +  do_test without_rowid3-14.2.2.6 {
         1000  +    catchsql { UPDATE t4 SET b = 1 }
         1001  +  } {0 {}}
         1002  +  do_test without_rowid3-14.2.2.7 {
         1003  +    execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
         1004  +  } {}
         1005  +
         1006  +  # Repeat for TEMP tables
         1007  +  #
         1008  +  drop_all_tables
         1009  +  do_test without_rowid3-14.1tmp.1 {
         1010  +    # Adding a column with a REFERENCES clause is not supported.
         1011  +    execsql { 
         1012  +      CREATE TEMP TABLE t1(a PRIMARY KEY) WITHOUT rowid;
         1013  +      CREATE TEMP TABLE t2(a, b);
         1014  +    }
         1015  +    catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
         1016  +  } {0 {}}
         1017  +  do_test without_rowid3-14.1tmp.2 {
         1018  +    catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
         1019  +  } {0 {}}
         1020  +  do_test without_rowid3-14.1tmp.3 {
         1021  +    catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
         1022  +  } {0 {}}
         1023  +  do_test without_rowid3-14.1tmp.4 {
         1024  +    catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
         1025  +  } {1 {Cannot add a REFERENCES column with non-NULL default value}}
         1026  +  do_test without_rowid3-14.1tmp.5 {
         1027  +    catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
         1028  +  } {1 {Cannot add a REFERENCES column with non-NULL default value}}
         1029  +  do_test without_rowid3-14.1tmp.6 {
         1030  +    execsql { 
         1031  +      PRAGMA foreign_keys = off;
         1032  +      ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
         1033  +      PRAGMA foreign_keys = on;
         1034  +      SELECT sql FROM sqlite_temp_master WHERE name='t2';
         1035  +    }
         1036  +  } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
         1037  +
         1038  +  do_test without_rowid3-14.2tmp.1.1 {
         1039  +    test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
         1040  +  } {{CREATE TABLE t1(a REFERENCES "t3")}}
         1041  +  do_test without_rowid3-14.2tmp.1.2 {
         1042  +    test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
         1043  +  } {{CREATE TABLE t1(a REFERENCES t2)}}
         1044  +  do_test without_rowid3-14.2tmp.1.3 {
         1045  +    test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
         1046  +  } {{CREATE TABLE t1(a REFERENCES "t3")}}
         1047  +  
         1048  +  # Test ALTER TABLE RENAME TABLE a bit.
         1049  +  #
         1050  +  do_test without_rowid3-14.2tmp.2.1 {
         1051  +    drop_all_tables
         1052  +    execsql {
         1053  +      CREATE TEMP TABLE t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid;
         1054  +      CREATE TEMP TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)
         1055  +            WITHOUT rowid;
         1056  +      CREATE TEMP TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
         1057  +    }
         1058  +    execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'}
         1059  +  } [list \
         1060  +    {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid}       \
         1061  +    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)
         1062  +            WITHOUT rowid}    \
         1063  +    {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)}  \
         1064  +  ]
         1065  +  do_test without_rowid3-14.2tmp.2.2 {
         1066  +    execsql { ALTER TABLE t1 RENAME TO t4 }
         1067  +    execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'}
         1068  +  } [list \
         1069  +    {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4") WITHOUT rowid}      \
         1070  +    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)
         1071  +            WITHOUT rowid}     \
         1072  +    {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
         1073  +  ]
         1074  +  do_test without_rowid3-14.2tmp.2.3 {
         1075  +    catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
         1076  +  } {1 {foreign key constraint failed}}
         1077  +  do_test without_rowid3-14.2tmp.2.4 {
         1078  +    execsql { INSERT INTO t4 VALUES(1, NULL) }
         1079  +  } {}
         1080  +  do_test without_rowid3-14.2tmp.2.5 {
         1081  +    catchsql { UPDATE t4 SET b = 5 }
         1082  +  } {1 {foreign key constraint failed}}
         1083  +  do_test without_rowid3-14.2tmp.2.6 {
         1084  +    catchsql { UPDATE t4 SET b = 1 }
         1085  +  } {0 {}}
         1086  +  do_test without_rowid3-14.2tmp.2.7 {
         1087  +    execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
         1088  +  } {}
         1089  +
         1090  +  # Repeat for ATTACH-ed tables
         1091  +  #
         1092  +  drop_all_tables
         1093  +  do_test without_rowid3-14.1aux.1 {
         1094  +    # Adding a column with a REFERENCES clause is not supported.
         1095  +    execsql { 
         1096  +      ATTACH ':memory:' AS aux;
         1097  +      CREATE TABLE aux.t1(a PRIMARY KEY) WITHOUT rowid;
         1098  +      CREATE TABLE aux.t2(a, b);
         1099  +    }
         1100  +    catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
         1101  +  } {0 {}}
         1102  +  do_test without_rowid3-14.1aux.2 {
         1103  +    catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
         1104  +  } {0 {}}
         1105  +  do_test without_rowid3-14.1aux.3 {
         1106  +    catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
         1107  +  } {0 {}}
         1108  +  do_test without_rowid3-14.1aux.4 {
         1109  +    catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
         1110  +  } {1 {Cannot add a REFERENCES column with non-NULL default value}}
         1111  +  do_test without_rowid3-14.1aux.5 {
         1112  +    catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
         1113  +  } {1 {Cannot add a REFERENCES column with non-NULL default value}}
         1114  +  do_test without_rowid3-14.1aux.6 {
         1115  +    execsql { 
         1116  +      PRAGMA foreign_keys = off;
         1117  +      ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
         1118  +      PRAGMA foreign_keys = on;
         1119  +      SELECT sql FROM aux.sqlite_master WHERE name='t2';
         1120  +    }
         1121  +  } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
         1122  +
         1123  +  do_test without_rowid3-14.2aux.1.1 {
         1124  +    test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
         1125  +  } {{CREATE TABLE t1(a REFERENCES "t3")}}
         1126  +  do_test without_rowid3-14.2aux.1.2 {
         1127  +    test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
         1128  +  } {{CREATE TABLE t1(a REFERENCES t2)}}
         1129  +  do_test without_rowid3-14.2aux.1.3 {
         1130  +    test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
         1131  +  } {{CREATE TABLE t1(a REFERENCES "t3")}}
         1132  +  
         1133  +  # Test ALTER TABLE RENAME TABLE a bit.
         1134  +  #
         1135  +  do_test without_rowid3-14.2aux.2.1 {
         1136  +    drop_all_tables
         1137  +    execsql {
         1138  +      CREATE TABLE aux.t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid;
         1139  +      CREATE TABLE aux.t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)
         1140  +            WITHOUT rowid;
         1141  +      CREATE TABLE aux.t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
         1142  +    }
         1143  +    execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
         1144  +  } [list \
         1145  +    {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid}       \
         1146  +    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)
         1147  +            WITHOUT rowid}    \
         1148  +    {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)}  \
         1149  +  ]
         1150  +  do_test without_rowid3-14.2aux.2.2 {
         1151  +    execsql { ALTER TABLE t1 RENAME TO t4 }
         1152  +    execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
         1153  +  } [list \
         1154  +    {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4") WITHOUT rowid}      \
         1155  +    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)
         1156  +            WITHOUT rowid}     \
         1157  +    {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
         1158  +  ]
         1159  +  do_test without_rowid3-14.2aux.2.3 {
         1160  +    catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
         1161  +  } {1 {foreign key constraint failed}}
         1162  +  do_test without_rowid3-14.2aux.2.4 {
         1163  +    execsql { INSERT INTO t4 VALUES(1, NULL) }
         1164  +  } {}
         1165  +  do_test without_rowid3-14.2aux.2.5 {
         1166  +    catchsql { UPDATE t4 SET b = 5 }
         1167  +  } {1 {foreign key constraint failed}}
         1168  +  do_test without_rowid3-14.2aux.2.6 {
         1169  +    catchsql { UPDATE t4 SET b = 1 }
         1170  +  } {0 {}}
         1171  +  do_test without_rowid3-14.2aux.2.7 {
         1172  +    execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
         1173  +  } {}
         1174  +}
         1175  +
         1176  +do_test without_rowid3-2.14.3.1 {
         1177  +  drop_all_tables
         1178  +  execsql {
         1179  +    CREATE TABLE t1(a, b REFERENCES nosuchtable);
         1180  +    DROP TABLE t1;
         1181  +  }
         1182  +} {}
         1183  +do_test without_rowid3-2.14.3.2 {
         1184  +  execsql {
         1185  +    CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid;
         1186  +    INSERT INTO t1 VALUES('a', 1);
         1187  +    CREATE TABLE t2(x REFERENCES t1);
         1188  +    INSERT INTO t2 VALUES('a');
         1189  +  }
         1190  +} {}
         1191  +do_test without_rowid3-2.14.3.3 {
         1192  +  catchsql { DROP TABLE t1 }
         1193  +} {1 {foreign key constraint failed}}
         1194  +do_test without_rowid3-2.14.3.4 {
         1195  +  execsql {
         1196  +    DELETE FROM t2;
         1197  +    DROP TABLE t1;
         1198  +  }
         1199  +} {}
         1200  +do_test without_rowid3-2.14.3.4 {
         1201  +  catchsql { INSERT INTO t2 VALUES('x') }
         1202  +} {1 {no such table: main.t1}}
         1203  +do_test without_rowid3-2.14.3.5 {
         1204  +  execsql {
         1205  +    CREATE TABLE t1(x PRIMARY KEY) WITHOUT rowid;
         1206  +    INSERT INTO t1 VALUES('x');
         1207  +  }
         1208  +  execsql { INSERT INTO t2 VALUES('x') }
         1209  +} {}
         1210  +do_test without_rowid3-2.14.3.6 {
         1211  +  catchsql { DROP TABLE t1 }
         1212  +} {1 {foreign key constraint failed}}
         1213  +do_test without_rowid3-2.14.3.7 {
         1214  +  execsql {
         1215  +    DROP TABLE t2;
         1216  +    DROP TABLE t1;
         1217  +  }
         1218  +} {}
         1219  +do_test without_rowid3-2.14.3.8 {
         1220  +  execsql {
         1221  +    CREATE TABLE pp(x, y, PRIMARY KEY(x, y)) WITHOUT ROWID;
         1222  +    CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
         1223  +  }
         1224  +  catchsql { INSERT INTO cc VALUES(1, 2) }
         1225  +} {1 {foreign key mismatch - "cc" referencing "pp"}}
         1226  +do_test without_rowid3-2.14.3.9 {
         1227  +  execsql { DROP TABLE cc }
         1228  +} {}
         1229  +do_test without_rowid3-2.14.3.10 {
         1230  +  execsql {
         1231  +    CREATE TABLE cc(a, b, 
         1232  +      FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED
         1233  +    );
         1234  +  }
         1235  +  execsql {
         1236  +    INSERT INTO pp VALUES('a', 'b');
         1237  +    INSERT INTO cc VALUES('a', 'b');
         1238  +    BEGIN;
         1239  +      DROP TABLE pp;
         1240  +      CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c)) WITHOUT rowid;
         1241  +      INSERT INTO pp VALUES(1, 'a', 'b');
         1242  +    COMMIT;
         1243  +  }
         1244  +} {}
         1245  +do_test without_rowid3-2.14.3.11 {
         1246  +  execsql { 
         1247  +    BEGIN;
         1248  +      DROP TABLE cc;
         1249  +      DROP TABLE pp;
         1250  +    COMMIT;
         1251  +  }
         1252  +} {}
         1253  +do_test without_rowid3-2.14.3.12 {
         1254  +  execsql {
         1255  +    CREATE TABLE b1(a, b);
         1256  +    CREATE TABLE b2(a, b REFERENCES b1);
         1257  +    DROP TABLE b1;
         1258  +  }
         1259  +} {}
         1260  +do_test without_rowid3-2.14.3.13 {
         1261  +  execsql {
         1262  +    CREATE TABLE b3(a, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED);
         1263  +    DROP TABLE b2;
         1264  +  }
         1265  +} {}
         1266  +
         1267  +# Test that nothing goes wrong when dropping a table that refers to a view.
         1268  +# Or dropping a view that an existing FK (incorrectly) refers to. Or either
         1269  +# of the above scenarios with a virtual table.
         1270  +drop_all_tables
         1271  +do_test without_rowid3-2.14.4.1 {
         1272  +  execsql {
         1273  +    CREATE TABLE t1(x REFERENCES v); 
         1274  +    CREATE VIEW v AS SELECT * FROM t1;
         1275  +  }
         1276  +} {}
         1277  +do_test without_rowid3-2.14.4.2 {
         1278  +  execsql {
         1279  +    DROP VIEW v;
         1280  +  }
         1281  +} {}
         1282  +ifcapable vtab {
         1283  +  register_echo_module db
         1284  +  do_test without_rowid3-2.14.4.3 {
         1285  +    execsql { CREATE VIRTUAL TABLE v USING echo(t1) }
         1286  +  } {}
         1287  +  do_test without_rowid3-2.14.4.2 {
         1288  +    execsql {
         1289  +      DROP TABLE v;
         1290  +    }
         1291  +  } {}
         1292  +}
         1293  +
         1294  +#-------------------------------------------------------------------------
         1295  +# The following tests, without_rowid3-15.*, test that unnecessary FK related scans 
         1296  +# and lookups are avoided when the constraint counters are zero.
         1297  +#
         1298  +drop_all_tables
         1299  +proc execsqlS {zSql} {
         1300  +  set ::sqlite_search_count 0
         1301  +  set ::sqlite_found_count 0
         1302  +  set res [uplevel [list execsql $zSql]]
         1303  +  concat [expr $::sqlite_found_count + $::sqlite_search_count] $res
         1304  +}
         1305  +do_test without_rowid3-15.1.1 {
         1306  +  execsql {
         1307  +    CREATE TABLE pp(a PRIMARY KEY, b) WITHOUT rowid;
         1308  +    CREATE TABLE cc(x, y REFERENCES pp DEFERRABLE INITIALLY DEFERRED);
         1309  +    INSERT INTO pp VALUES(1, 'one');
         1310  +    INSERT INTO pp VALUES(2, 'two');
         1311  +    INSERT INTO cc VALUES('neung', 1);
         1312  +    INSERT INTO cc VALUES('song', 2);
         1313  +  }
         1314  +} {}
         1315  +do_test without_rowid3-15.1.2 {
         1316  +  execsqlS { INSERT INTO pp VALUES(3, 'three') }
         1317  +} {0}
         1318  +do_test without_rowid3-15.1.3 {
         1319  +  execsql {
         1320  +    BEGIN;
         1321  +      INSERT INTO cc VALUES('see', 4);    -- Violates deferred constraint
         1322  +  }
         1323  +  execsqlS { INSERT INTO pp VALUES(5, 'five') }
         1324  +} {2}
         1325  +do_test without_rowid3-15.1.4 {
         1326  +  execsql { DELETE FROM cc WHERE x = 'see' }
         1327  +  execsqlS { INSERT INTO pp VALUES(6, 'six') }
         1328  +} {0}
         1329  +do_test without_rowid3-15.1.5 {
         1330  +  execsql COMMIT
         1331  +} {}
         1332  +do_test without_rowid3-15.1.6 {
         1333  +  execsql BEGIN
         1334  +  execsqlS {
         1335  +    DELETE FROM cc WHERE x = 'neung';
         1336  +    ROLLBACK;
         1337  +  }
         1338  +} {1}
         1339  +do_test without_rowid3-15.1.7 {
         1340  +  execsql { 
         1341  +    BEGIN;
         1342  +    DELETE FROM pp WHERE a = 2;
         1343  +  }
         1344  +  execsqlS {
         1345  +    DELETE FROM cc WHERE x = 'neung';
         1346  +    ROLLBACK;
         1347  +  }
         1348  +} {2}
         1349  +
         1350  +#-------------------------------------------------------------------------
         1351  +# This next block of tests, without_rowid3-16.*, test that rows that refer to
         1352  +# themselves may be inserted and deleted.
         1353  +#
         1354  +foreach {tn zSchema} {
         1355  +  1 { CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a))
         1356  +             WITHOUT rowid }
         1357  +  2 { CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)) WITHOUT rowid }
         1358  +  3 { CREATE TABLE self(a UNIQUE, b INT PRIMARY KEY REFERENCES self(a))
         1359  +             WITHOUT rowid }
         1360  +} {
         1361  +  drop_all_tables
         1362  +  do_test without_rowid3-16.1.$tn.1 {
         1363  +    execsql $zSchema
         1364  +    execsql { INSERT INTO self VALUES(13, 13) }
         1365  +  } {}
         1366  +  do_test without_rowid3-16.1.$tn.2 {
         1367  +    execsql { UPDATE self SET a = 14, b = 14 }
         1368  +  } {}
         1369  +
         1370  +  do_test without_rowid3-16.1.$tn.3 {
         1371  +    catchsql { UPDATE self SET b = 15 }
         1372  +  } {1 {foreign key constraint failed}}
         1373  +
         1374  +  do_test without_rowid3-16.1.$tn.4 {
         1375  +    catchsql { UPDATE self SET a = 15 }
         1376  +  } {1 {foreign key constraint failed}}
         1377  +
         1378  +  do_test without_rowid3-16.1.$tn.5 {
         1379  +    catchsql { UPDATE self SET a = 15, b = 16 }
         1380  +  } {1 {foreign key constraint failed}}
         1381  +
         1382  +  do_test without_rowid3-16.1.$tn.6 {
         1383  +    catchsql { UPDATE self SET a = 17, b = 17 }
         1384  +  } {0 {}}
         1385  +
         1386  +  do_test without_rowid3-16.1.$tn.7 {
         1387  +    execsql { DELETE FROM self }
         1388  +  } {}
         1389  +  do_test without_rowid3-16.1.$tn.8 {
         1390  +    catchsql { INSERT INTO self VALUES(20, 21) }
         1391  +  } {1 {foreign key constraint failed}}
         1392  +}
         1393  +
         1394  +#-------------------------------------------------------------------------
         1395  +# This next block of tests, without_rowid3-17.*, tests that if "PRAGMA count_changes"
         1396  +# is turned on statements that violate immediate FK constraints return
         1397  +# SQLITE_CONSTRAINT immediately, not after returning a number of rows.
         1398  +# Whereas statements that violate deferred FK constraints return the number
         1399  +# of rows before failing.
         1400  +#
         1401  +# Also test that rows modified by FK actions are not counted in either the
         1402  +# returned row count or the values returned by sqlite3_changes(). Like
         1403  +# trigger related changes, they are included in sqlite3_total_changes() though.
         1404  +#
         1405  +drop_all_tables
         1406  +do_test without_rowid3-17.1.1 {
         1407  +  execsql { PRAGMA count_changes = 1 }
         1408  +  execsql { 
         1409  +    CREATE TABLE one(a, b, c, UNIQUE(b, c));
         1410  +    CREATE TABLE two(d, e, f, FOREIGN KEY(e, f) REFERENCES one(b, c));
         1411  +    INSERT INTO one VALUES(1, 2, 3);
         1412  +  }
         1413  +} {1}
         1414  +do_test without_rowid3-17.1.2 {
         1415  +  set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy]
         1416  +  sqlite3_step $STMT
         1417  +} {SQLITE_CONSTRAINT}
         1418  +verify_ex_errcode without_rowid3-17.1.2b SQLITE_CONSTRAINT_FOREIGNKEY
         1419  +ifcapable autoreset {
         1420  +  do_test without_rowid3-17.1.3 {
         1421  +    sqlite3_step $STMT
         1422  +  } {SQLITE_CONSTRAINT}
         1423  +  verify_ex_errcode without_rowid3-17.1.3b SQLITE_CONSTRAINT_FOREIGNKEY
         1424  +} else {
         1425  +  do_test without_rowid3-17.1.3 {
         1426  +    sqlite3_step $STMT
         1427  +  } {SQLITE_MISUSE}
         1428  +}
         1429  +do_test without_rowid3-17.1.4 {
         1430  +  sqlite3_finalize $STMT
         1431  +} {SQLITE_CONSTRAINT}
         1432  +verify_ex_errcode without_rowid3-17.1.4b SQLITE_CONSTRAINT_FOREIGNKEY
         1433  +do_test without_rowid3-17.1.5 {
         1434  +  execsql {
         1435  +    INSERT INTO one VALUES(2, 3, 4);
         1436  +    INSERT INTO one VALUES(3, 4, 5);
         1437  +    INSERT INTO two VALUES(1, 2, 3);
         1438  +    INSERT INTO two VALUES(2, 3, 4);
         1439  +    INSERT INTO two VALUES(3, 4, 5);
         1440  +  }
         1441  +} {1 1 1 1 1}
         1442  +do_test without_rowid3-17.1.6 {
         1443  +  catchsql {
         1444  +    BEGIN;
         1445  +      INSERT INTO one VALUES(0, 0, 0);
         1446  +      UPDATE two SET e=e+1, f=f+1;
         1447  +  }
         1448  +} {1 {foreign key constraint failed}}
         1449  +do_test without_rowid3-17.1.7 {
         1450  +  execsql { SELECT * FROM one }
         1451  +} {1 2 3 2 3 4 3 4 5 0 0 0}
         1452  +do_test without_rowid3-17.1.8 {
         1453  +  execsql { SELECT * FROM two }
         1454  +} {1 2 3 2 3 4 3 4 5}
         1455  +do_test without_rowid3-17.1.9 {
         1456  +  execsql COMMIT
         1457  +} {}
         1458  +do_test without_rowid3-17.1.10 {
         1459  +  execsql {
         1460  +    CREATE TABLE three(
         1461  +      g, h, i, 
         1462  +      FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED
         1463  +    );
         1464  +  }
         1465  +} {}
         1466  +do_test without_rowid3-17.1.11 {
         1467  +  set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy]
         1468  +  sqlite3_step $STMT
         1469  +} {SQLITE_ROW}
         1470  +do_test without_rowid3-17.1.12 {
         1471  +  sqlite3_column_text $STMT 0
         1472  +} {1}
         1473  +do_test without_rowid3-17.1.13 {
         1474  +  sqlite3_step $STMT
         1475  +} {SQLITE_CONSTRAINT}
         1476  +verify_ex_errcode without_rowid3-17.1.13b SQLITE_CONSTRAINT_FOREIGNKEY
         1477  +do_test without_rowid3-17.1.14 {
         1478  +  sqlite3_finalize $STMT
         1479  +} {SQLITE_CONSTRAINT}
         1480  +verify_ex_errcode without_rowid3-17.1.14b SQLITE_CONSTRAINT_FOREIGNKEY
         1481  +
         1482  +drop_all_tables
         1483  +do_test without_rowid3-17.2.1 {
         1484  +  execsql {
         1485  +    CREATE TABLE high("a'b!" PRIMARY KEY, b) WITHOUT rowid;
         1486  +    CREATE TABLE low(
         1487  +      c, 
         1488  +      "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE
         1489  +    );
         1490  +  }
         1491  +} {}
         1492  +do_test without_rowid3-17.2.2 {
         1493  +  execsql {
         1494  +    INSERT INTO high VALUES('a', 'b');
         1495  +    INSERT INTO low VALUES('b', 'a');
         1496  +  }
         1497  +  db changes
         1498  +} {1}
         1499  +set nTotal [db total_changes]
         1500  +do_test without_rowid3-17.2.3 {
         1501  +  execsql { UPDATE high SET "a'b!" = 'c' }
         1502  +} {1}
         1503  +do_test without_rowid3-17.2.4 {
         1504  +  db changes
         1505  +} {1}
         1506  +do_test without_rowid3-17.2.5 {
         1507  +  expr [db total_changes] - $nTotal
         1508  +} {2}
         1509  +do_test without_rowid3-17.2.6 {
         1510  +  execsql { SELECT * FROM high ; SELECT * FROM low }
         1511  +} {c b b c}
         1512  +do_test without_rowid3-17.2.7 {
         1513  +  execsql { DELETE FROM high }
         1514  +} {1}
         1515  +do_test without_rowid3-17.2.8 {
         1516  +  db changes
         1517  +} {1}
         1518  +do_test without_rowid3-17.2.9 {
         1519  +  expr [db total_changes] - $nTotal
         1520  +} {4}
         1521  +do_test without_rowid3-17.2.10 {
         1522  +  execsql { SELECT * FROM high ; SELECT * FROM low }
         1523  +} {}
         1524  +execsql { PRAGMA count_changes = 0 }
         1525  +
         1526  +#-------------------------------------------------------------------------
         1527  +# Test that the authorization callback works.
         1528  +#
         1529  +
         1530  +ifcapable auth {
         1531  +  do_test without_rowid3-18.1 {
         1532  +    execsql {
         1533  +      CREATE TABLE long(a, b PRIMARY KEY, c) WITHOUT rowid;
         1534  +      CREATE TABLE short(d, e, f REFERENCES long);
         1535  +      CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED);
         1536  +    }
         1537  +  } {}
         1538  +
         1539  +  proc auth {args} {eval lappend ::authargs $args ; return SQLITE_OK}
         1540  +  db auth auth
         1541  +
         1542  +  # An insert on the parent table must read the child key of any deferred
         1543  +  # foreign key constraints. But not the child key of immediate constraints.
         1544  +  set authargs {}
         1545  +  do_test without_rowid3-18.2 {
         1546  +    execsql { INSERT INTO long VALUES(1, 2, 3) }
         1547  +    set authargs
         1548  +  } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}}
         1549  +
         1550  +  # An insert on the child table of an immediate constraint must read the
         1551  +  # parent key columns (to see if it is a violation or not).
         1552  +  set authargs {}
         1553  +  do_test without_rowid3-18.3 {
         1554  +    execsql { INSERT INTO short VALUES(1, 3, 2) }
         1555  +    set authargs
         1556  +  } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}}
         1557  +  
         1558  +  # As must an insert on the child table of a deferred constraint.
         1559  +  set authargs {}
         1560  +  do_test without_rowid3-18.4 {
         1561  +    execsql { INSERT INTO mid VALUES(1, 3, 2) }
         1562  +    set authargs
         1563  +  } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}}
         1564  +
         1565  +  do_test without_rowid3-18.5 {
         1566  +    execsql {
         1567  +      CREATE TABLE nought(a, b PRIMARY KEY, c) WITHOUT rowid;
         1568  +      CREATE TABLE cross(d, e, f,
         1569  +        FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE
         1570  +      );
         1571  +    }
         1572  +    execsql { INSERT INTO nought VALUES(2, 1, 2) }
         1573  +    execsql { INSERT INTO cross VALUES(0, 1, 0) }
         1574  +    set authargs [list]
         1575  +    execsql { UPDATE nought SET b = 5 }
         1576  +    set authargs
         1577  +  } {SQLITE_UPDATE nought b main {} SQLITE_READ cross e main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_UPDATE cross e main {} SQLITE_READ nought b main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {}}
         1578  +
         1579  +  do_test without_rowid3-18.6 {
         1580  +    execsql {SELECT * FROM cross}
         1581  +  } {0 5 0}
         1582  +
         1583  +  do_test without_rowid3-18.7 {
         1584  +    execsql {
         1585  +      CREATE TABLE one(a INT PRIMARY KEY, b) WITHOUT rowid;
         1586  +      CREATE TABLE two(b, c REFERENCES one);
         1587  +      INSERT INTO one VALUES(101, 102);
         1588  +    }
         1589  +    set authargs [list]
         1590  +    execsql { INSERT INTO two VALUES(100, 101); }
         1591  +    set authargs
         1592  +  } {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}}
         1593  +
         1594  +  # Return SQLITE_IGNORE to requests to read from the parent table. This
         1595  +  # causes inserts of non-NULL keys into the child table to fail.
         1596  +  #
         1597  +  rename auth {}
         1598  +  proc auth {args} {
         1599  +    if {[lindex $args 1] == "long"} {return SQLITE_IGNORE}
         1600  +    return SQLITE_OK
         1601  +  }
         1602  +  do_test without_rowid3-18.8 {
         1603  +    catchsql { INSERT INTO short VALUES(1, 3, 2) }
         1604  +  } {1 {foreign key constraint failed}}
         1605  +  do_test without_rowid3-18.9 {
         1606  +    execsql { INSERT INTO short VALUES(1, 3, NULL) }
         1607  +  } {}
         1608  +  do_test without_rowid3-18.10 {
         1609  +    execsql { SELECT * FROM short }
         1610  +  } {1 3 2 1 3 {}}
         1611  +  do_test without_rowid3-18.11 {
         1612  +    catchsql { UPDATE short SET f = 2 WHERE f IS NULL }
         1613  +  } {1 {foreign key constraint failed}}
         1614  +
         1615  +  db auth {}
         1616  +  unset authargs
         1617  +}
         1618  +
         1619  +
         1620  +do_test without_rowid3-19.1 {
         1621  +  execsql {
         1622  +    CREATE TABLE main(id INT PRIMARY KEY) WITHOUT rowid;
         1623  +    CREATE TABLE sub(id INT REFERENCES main(id));
         1624  +    INSERT INTO main VALUES(1);
         1625  +    INSERT INTO main VALUES(2);
         1626  +    INSERT INTO sub VALUES(2);
         1627  +  }
         1628  +} {}
         1629  +do_test without_rowid3-19.2 {
         1630  +  set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy]
         1631  +  sqlite3_bind_int $S 1 2
         1632  +  sqlite3_step $S
         1633  +} {SQLITE_CONSTRAINT}
         1634  +verify_ex_errcode without_rowid3-19.2b SQLITE_CONSTRAINT_FOREIGNKEY
         1635  +do_test without_rowid3-19.3 {
         1636  +  sqlite3_reset $S
         1637  +} {SQLITE_CONSTRAINT}
         1638  +verify_ex_errcode without_rowid3-19.3b SQLITE_CONSTRAINT_FOREIGNKEY
         1639  +do_test without_rowid3-19.4 {
         1640  +  sqlite3_bind_int $S 1 1
         1641  +  sqlite3_step $S
         1642  +} {SQLITE_DONE}
         1643  +do_test without_rowid3-19.4 {
         1644  +  sqlite3_finalize $S
         1645  +} {SQLITE_OK}
         1646  +
         1647  +drop_all_tables
         1648  +do_test without_rowid3-20.1 {
         1649  +  execsql {
         1650  +    CREATE TABLE pp(a PRIMARY KEY, b) WITHOUT rowid;
         1651  +    CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp) WITHOUT rowid;
         1652  +  }
         1653  +} {}
         1654  +
         1655  +foreach {tn insert} {
         1656  +  1 "INSERT"
         1657  +  2 "INSERT OR IGNORE"
         1658  +  3 "INSERT OR ABORT"
         1659  +  4 "INSERT OR ROLLBACK"
         1660  +  5 "INSERT OR REPLACE"
         1661  +  6 "INSERT OR FAIL"
         1662  +} {
         1663  +  do_test without_rowid3-20.2.$tn.1 {
         1664  +    catchsql "$insert INTO cc VALUES(1, 2)"
         1665  +  } {1 {foreign key constraint failed}}
         1666  +  do_test without_rowid3-20.2.$tn.2 {
         1667  +    execsql { SELECT * FROM cc }
         1668  +  } {}
         1669  +  do_test without_rowid3-20.2.$tn.3 {
         1670  +    execsql {
         1671  +      BEGIN;
         1672  +        INSERT INTO pp VALUES(2, 'two');
         1673  +        INSERT INTO cc VALUES(1, 2);
         1674  +    }
         1675  +    catchsql "$insert INTO cc VALUES(3, 4)"
         1676  +  } {1 {foreign key constraint failed}}
         1677  +  do_test without_rowid3-20.2.$tn.4 {
         1678  +    execsql { COMMIT ; SELECT * FROM cc }
         1679  +  } {1 2}
         1680  +  do_test without_rowid3-20.2.$tn.5 {
         1681  +    execsql { DELETE FROM cc ; DELETE FROM pp }
         1682  +  } {}
         1683  +}
         1684  +
         1685  +foreach {tn update} {
         1686  +  1 "UPDATE"
         1687  +  2 "UPDATE OR IGNORE"
         1688  +  3 "UPDATE OR ABORT"
         1689  +  4 "UPDATE OR ROLLBACK"
         1690  +  5 "UPDATE OR REPLACE"
         1691  +  6 "UPDATE OR FAIL"
         1692  +} {
         1693  +  do_test without_rowid3-20.3.$tn.1 {
         1694  +    execsql {
         1695  +      INSERT INTO pp VALUES(2, 'two');
         1696  +      INSERT INTO cc VALUES(1, 2);
         1697  +    }
         1698  +  } {}
         1699  +  do_test without_rowid3-20.3.$tn.2 {
         1700  +    catchsql "$update pp SET a = 1"
         1701  +  } {1 {foreign key constraint failed}}
         1702  +  do_test without_rowid3-20.3.$tn.3 {
         1703  +    execsql { SELECT * FROM pp }
         1704  +  } {2 two}
         1705  +  do_test without_rowid3-20.3.$tn.4 {
         1706  +    catchsql "$update cc SET d = 1"
         1707  +  } {1 {foreign key constraint failed}}
         1708  +  do_test without_rowid3-20.3.$tn.5 {
         1709  +    execsql { SELECT * FROM cc }
         1710  +  } {1 2}
         1711  +  do_test without_rowid3-20.3.$tn.6 {
         1712  +    execsql {
         1713  +      BEGIN;
         1714  +        INSERT INTO pp VALUES(3, 'three');
         1715  +    }
         1716  +    catchsql "$update pp SET a = 1 WHERE a = 2"
         1717  +  } {1 {foreign key constraint failed}}
         1718  +  do_test without_rowid3-20.3.$tn.7 {
         1719  +    execsql { COMMIT ; SELECT * FROM pp }
         1720  +  } {2 two 3 three}
         1721  +  do_test without_rowid3-20.3.$tn.8 {
         1722  +    execsql {
         1723  +      BEGIN;
         1724  +        INSERT INTO cc VALUES(2, 2);
         1725  +    }
         1726  +    catchsql "$update cc SET d = 1 WHERE c = 1"
         1727  +  } {1 {foreign key constraint failed}}
         1728  +  do_test without_rowid3-20.3.$tn.9 {
         1729  +    execsql { COMMIT ; SELECT * FROM cc }
         1730  +  } {1 2 2 2}
         1731  +  do_test without_rowid3-20.3.$tn.10 {
         1732  +    execsql { DELETE FROM cc ; DELETE FROM pp }
         1733  +  } {}
         1734  +}
         1735  +
         1736  +#-------------------------------------------------------------------------
         1737  +# The following block of tests, those prefixed with "without_rowid3-genfkey.",
         1738  +# are the same tests that were used to test the ".genfkey" command provided 
         1739  +# by the shell tool. So these tests show that the built-in foreign key 
         1740  +# implementation is more or less compatible with the triggers generated 
         1741  +# by genfkey.
         1742  +#
         1743  +drop_all_tables
         1744  +do_test without_rowid3-genfkey.1.1 {
         1745  +  execsql {
         1746  +    CREATE TABLE t1(a INT PRIMARY KEY, b, c, UNIQUE(b, c)) WITHOUT rowid;
         1747  +    CREATE TABLE t2(e REFERENCES t1, f);
         1748  +    CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));
         1749  +  }
         1750  +} {}
         1751  +do_test without_rowid3-genfkey.1.2 {
         1752  +  catchsql { INSERT INTO t2 VALUES(1, 2) }
         1753  +} {1 {foreign key constraint failed}}
         1754  +do_test without_rowid3-genfkey.1.3 {
         1755  +  execsql {
         1756  +    INSERT INTO t1 VALUES(1, 2, 3);
         1757  +    INSERT INTO t2 VALUES(1, 2);
         1758  +  }
         1759  +} {}
         1760  +do_test without_rowid3-genfkey.1.4 {
         1761  +  execsql { INSERT INTO t2 VALUES(NULL, 3) }
         1762  +} {}
         1763  +do_test without_rowid3-genfkey.1.5 {
         1764  +  catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL }
         1765  +} {1 {foreign key constraint failed}}
         1766  +do_test without_rowid3-genfkey.1.6 {
         1767  +  execsql { UPDATE t2 SET e = 1 WHERE e IS NULL }
         1768  +} {}
         1769  +do_test without_rowid3-genfkey.1.7 {
         1770  +  execsql { UPDATE t2 SET e = NULL WHERE f = 3 }
         1771  +} {}
         1772  +do_test without_rowid3-genfkey.1.8 {
         1773  +  catchsql { UPDATE t1 SET a = 10 }
         1774  +} {1 {foreign key constraint failed}}
         1775  +do_test without_rowid3-genfkey.1.9 {
         1776  +  catchsql { UPDATE t1 SET a = NULL }
         1777  +} {1 {t1.a may not be NULL}}
         1778  +do_test without_rowid3-genfkey.1.10 {
         1779  +  catchsql { DELETE FROM t1 }
         1780  +} {1 {foreign key constraint failed}}
         1781  +do_test without_rowid3-genfkey.1.11 {
         1782  +  execsql { UPDATE t2 SET e = NULL }
         1783  +} {}
         1784  +do_test without_rowid3-genfkey.1.12 {
         1785  +  execsql { 
         1786  +    UPDATE t1 SET a = 10;
         1787  +    DELETE FROM t1;
         1788  +    DELETE FROM t2;
         1789  +  }
         1790  +} {}
         1791  +do_test without_rowid3-genfkey.1.13 {
         1792  +  execsql {
         1793  +    INSERT INTO t3 VALUES(1, NULL, NULL);
         1794  +    INSERT INTO t3 VALUES(1, 2, NULL);
         1795  +    INSERT INTO t3 VALUES(1, NULL, 3);
         1796  +  }
         1797  +} {}
         1798  +do_test without_rowid3-genfkey.1.14 {
         1799  +  catchsql { INSERT INTO t3 VALUES(3, 1, 4) }
         1800  +} {1 {foreign key constraint failed}}
         1801  +do_test without_rowid3-genfkey.1.15 {
         1802  +  execsql { 
         1803  +    INSERT INTO t1 VALUES(1, 1, 4);
         1804  +    INSERT INTO t3 VALUES(3, 1, 4);
         1805  +  }
         1806  +} {}
         1807  +do_test without_rowid3-genfkey.1.16 {
         1808  +  catchsql { DELETE FROM t1 }
         1809  +} {1 {foreign key constraint failed}}
         1810  +do_test without_rowid3-genfkey.1.17 {
         1811  +  catchsql { UPDATE t1 SET b = 10}
         1812  +} {1 {foreign key constraint failed}}
         1813  +do_test without_rowid3-genfkey.1.18 {
         1814  +  execsql { UPDATE t1 SET a = 10}
         1815  +} {}
         1816  +do_test without_rowid3-genfkey.1.19 {
         1817  +  catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3}
         1818  +} {1 {foreign key constraint failed}}
         1819  +
         1820  +drop_all_tables
         1821  +do_test without_rowid3-genfkey.2.1 {
         1822  +  execsql {
         1823  +    CREATE TABLE t1(a INT PRIMARY KEY, b, c, UNIQUE(b, c)) WITHOUT rowid;
         1824  +    CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f);
         1825  +    CREATE TABLE t3(g, h, i, 
         1826  +        FOREIGN KEY (h, i) 
         1827  +        REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE
         1828  +    );
         1829  +  }
         1830  +} {}
         1831  +do_test without_rowid3-genfkey.2.2 {
         1832  +  execsql {
         1833  +    INSERT INTO t1 VALUES(1, 2, 3);
         1834  +    INSERT INTO t1 VALUES(4, 5, 6);
         1835  +    INSERT INTO t2 VALUES(1, 'one');
         1836  +    INSERT INTO t2 VALUES(4, 'four');
         1837  +  }
         1838  +} {}
         1839  +do_test without_rowid3-genfkey.2.3 {
         1840  +  execsql {
         1841  +    UPDATE t1 SET a = 2 WHERE a = 1;
         1842  +    SELECT * FROM t2;
         1843  +  }
         1844  +} {2 one 4 four}
         1845  +do_test without_rowid3-genfkey.2.4 {
         1846  +  execsql {
         1847  +    DELETE FROM t1 WHERE a = 4;
         1848  +    SELECT * FROM t2;
         1849  +  }
         1850  +} {2 one}
         1851  +
         1852  +do_test without_rowid3-genfkey.2.5 {
         1853  +  execsql {
         1854  +    INSERT INTO t3 VALUES('hello', 2, 3);
         1855  +    UPDATE t1 SET c = 2;
         1856  +    SELECT * FROM t3;
         1857  +  }
         1858  +} {hello 2 2}
         1859  +do_test without_rowid3-genfkey.2.6 {
         1860  +  execsql {
         1861  +    DELETE FROM t1;
         1862  +    SELECT * FROM t3;
         1863  +  }
         1864  +} {}
         1865  +
         1866  +drop_all_tables
         1867  +do_test without_rowid3-genfkey.3.1 {
         1868  +  execsql {
         1869  +    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b)) WITHOUT rowid;
         1870  +    CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f);
         1871  +    CREATE TABLE t3(g, h, i, 
         1872  +        FOREIGN KEY (h, i) 
         1873  +        REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL
         1874  +    );
         1875  +  }
         1876  +} {}
         1877  +do_test without_rowid3-genfkey.3.2 {
         1878  +  execsql {
         1879  +    INSERT INTO t1 VALUES(1, 2, 3);
         1880  +    INSERT INTO t1 VALUES(4, 5, 6);
         1881  +    INSERT INTO t2 VALUES(1, 'one');
         1882  +    INSERT INTO t2 VALUES(4, 'four');
         1883  +  }
         1884  +} {}
         1885  +do_test without_rowid3-genfkey.3.3 {
         1886  +  execsql {
         1887  +    UPDATE t1 SET a = 2 WHERE a = 1;
         1888  +    SELECT * FROM t2;
         1889  +  }
         1890  +} {{} one 4 four}
         1891  +do_test without_rowid3-genfkey.3.4 {
         1892  +  execsql {
         1893  +    DELETE FROM t1 WHERE a = 4;
         1894  +    SELECT * FROM t2;
         1895  +  }
         1896  +} {{} one {} four}
         1897  +do_test without_rowid3-genfkey.3.5 {
         1898  +  execsql {
         1899  +    INSERT INTO t3 VALUES('hello', 2, 3);
         1900  +    UPDATE t1 SET c = 2;
         1901  +    SELECT * FROM t3;
         1902  +  }
         1903  +} {hello {} {}}
         1904  +do_test without_rowid3-genfkey.3.6 {
         1905  +  execsql {
         1906  +    UPDATE t3 SET h = 2, i = 2;
         1907  +    DELETE FROM t1;
         1908  +    SELECT * FROM t3;
         1909  +  }
         1910  +} {hello {} {}}
         1911  +
         1912  +#-------------------------------------------------------------------------
         1913  +# Verify that ticket dd08e5a988d00decc4a543daa8dbbfab9c577ad8 has been
         1914  +# fixed.
         1915  +#
         1916  +do_test without_rowid3-dd08e5.1.1 {
         1917  +  execsql {
         1918  +    PRAGMA foreign_keys=ON;
         1919  +    CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b) WITHOUT rowid;
         1920  +    CREATE UNIQUE INDEX idd08 ON tdd08(a,b);
         1921  +    INSERT INTO tdd08 VALUES(200,300);
         1922  +
         1923  +    CREATE TABLE tdd08_b(w,x,y, FOREIGN KEY(x,y) REFERENCES tdd08(a,b));
         1924  +    INSERT INTO tdd08_b VALUES(100,200,300);
         1925  +  }
         1926  +} {}
         1927  +do_test without_rowid3-dd08e5.1.2 {
         1928  +  catchsql {
         1929  +    DELETE FROM tdd08;
         1930  +  }
         1931  +} {1 {foreign key constraint failed}}
         1932  +do_test without_rowid3-dd08e5.1.3 {
         1933  +  execsql {
         1934  +    SELECT * FROM tdd08;
         1935  +  }
         1936  +} {200 300}
         1937  +do_test without_rowid3-dd08e5.1.4 {
         1938  +  catchsql {
         1939  +    INSERT INTO tdd08_b VALUES(400,500,300);
         1940  +  }
         1941  +} {1 {foreign key constraint failed}}
         1942  +do_test without_rowid3-dd08e5.1.5 {
         1943  +  catchsql {
         1944  +    UPDATE tdd08_b SET x=x+1;
         1945  +  }
         1946  +} {1 {foreign key constraint failed}}
         1947  +do_test without_rowid3-dd08e5.1.6 {
         1948  +  catchsql {
         1949  +    UPDATE tdd08 SET a=a+1;
         1950  +  }
         1951  +} {1 {foreign key constraint failed}}
         1952  +
         1953  +#-------------------------------------------------------------------------
         1954  +# Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba
         1955  +# fixed.
         1956  +#
         1957  +do_test without_rowid3-ce7c13.1.1 {
         1958  +  execsql {
         1959  +    CREATE TABLE tce71(a INTEGER PRIMARY KEY, b) WITHOUT rowid;
         1960  +    CREATE UNIQUE INDEX ice71 ON tce71(a,b);
         1961  +    INSERT INTO tce71 VALUES(100,200);
         1962  +    CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b));
         1963  +    INSERT INTO tce72 VALUES(300,100,200);
         1964  +    UPDATE tce71 set b = 200 where a = 100;
         1965  +    SELECT * FROM tce71, tce72;
         1966  +  }
         1967  +} {100 200 300 100 200}
         1968  +do_test without_rowid3-ce7c13.1.2 {
         1969  +  catchsql {
         1970  +    UPDATE tce71 set b = 201 where a = 100;
         1971  +  }
         1972  +} {1 {foreign key constraint failed}}
         1973  +do_test without_rowid3-ce7c13.1.3 {
         1974  +  catchsql {
         1975  +    UPDATE tce71 set a = 101 where a = 100;
         1976  +  }
         1977  +} {1 {foreign key constraint failed}}
         1978  +do_test without_rowid3-ce7c13.1.4 {
         1979  +  execsql {
         1980  +    CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b)) WITHOUT rowid;
         1981  +    INSERT INTO tce73 VALUES(100,200);
         1982  +    CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b));
         1983  +    INSERT INTO tce74 VALUES(300,100,200);
         1984  +    UPDATE tce73 set b = 200 where a = 100;
         1985  +    SELECT * FROM tce73, tce74;
         1986  +  }
         1987  +} {100 200 300 100 200}
         1988  +do_test without_rowid3-ce7c13.1.5 {
         1989  +  catchsql {
         1990  +    UPDATE tce73 set b = 201 where a = 100;
         1991  +  }
         1992  +} {1 {foreign key constraint failed}}
         1993  +do_test without_rowid3-ce7c13.1.6 {
         1994  +  catchsql {
         1995  +    UPDATE tce73 set a = 101 where a = 100;
         1996  +  }
         1997  +} {1 {foreign key constraint failed}}
         1998  +
         1999  +finish_test