/ Check-in [659c551d]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Do not commit an "OR FAIL" statement that causes foriegn key constraint violations.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 659c551dcc374a0d349ba9419f692e3363839e11d791a17cc35fa1854a674a51
User & Date: dan 2019-05-07 19:44:11
Context
2019-05-07
20:06
Generate all records for INSERT or UPDATE prior to running foreign key constraint checks, since the FK checks might modify the datatype of registers used to generate the records. Fix for ticket [e63cbcfd3378afe6980d6]. check-in: 3c75605b user: drh tags: trunk
19:44
Do not commit an "OR FAIL" statement that causes foriegn key constraint violations. check-in: 659c551d user: dan tags: trunk
17:47
Strive to prevent harmless compiler warnings in GCC 4.8.5. check-in: 8b6691f6 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/vdbeaux.c.

  2892   2892             db->autoCommit = 1;
  2893   2893             p->nChange = 0;
  2894   2894           }
  2895   2895         }
  2896   2896       }
  2897   2897   
  2898   2898       /* Check for immediate foreign key violations. */
  2899         -    if( p->rc==SQLITE_OK ){
         2899  +    if( p->rc==SQLITE_OK || (p->errorAction==OE_Fail && !isSpecialError) ){
  2900   2900         sqlite3VdbeCheckFk(p, 0);
  2901   2901       }
  2902   2902     
  2903   2903       /* If the auto-commit flag is set and this is the only active writer 
  2904   2904       ** VM, then we do either a commit or rollback of the current transaction. 
  2905   2905       **
  2906   2906       ** Note: This block also runs if one of the special errors handled 

Changes to test/fkey7.test.

    78     78       INSERT INTO c4 VALUES(1), (2), (3);
    79     79   
    80     80       ANALYZE;
    81     81       INSERT INTO p4(id) VALUES(4);
    82     82     }
    83     83   }
    84     84   
           85  +
           86  +do_execsql_test 4.0 {
           87  +  PRAGMA foreign_keys = true;
           88  +  CREATE TABLE parent(
           89  +    p PRIMARY KEY
           90  +  );
           91  +  CREATE TABLE child(
           92  +    c UNIQUE REFERENCES parent(p)
           93  +  );
           94  +}
           95  +
           96  +do_catchsql_test 4.1 {
           97  +  INSERT OR FAIL INTO child VALUES(123), (123);
           98  +} {1 {FOREIGN KEY constraint failed}}
           99  +
          100  +do_execsql_test 4.2 {
          101  +  SELECT * FROM child;
          102  +} {}
          103  +
          104  +do_execsql_test 4.3 {
          105  +  PRAGMA foreign_key_check;
          106  +} {}
          107  +
          108  +do_catchsql_test 4.4 {
          109  +  INSERT INTO parent VALUES(123);
          110  +  INSERT OR FAIL INTO child VALUES(123), (123);
          111  +} {1 {UNIQUE constraint failed: child.c}}
          112  +
          113  +do_execsql_test 4.5 {
          114  +  SELECT * FROM child;
          115  +} {123}
          116  +
          117  +do_execsql_test 4.6 {
          118  +  PRAGMA foreign_key_check;
          119  +} {}
          120  +
    85    121   finish_test