/ Check-in [850877d1]
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:Fix a problem causing SQLite to return false "foreign key violation" errors when there is a partial (i.e. WHERE constrained) UNIQUE index on the parent key columns. This bug did not cause SQLite to allow illegal data to be inserted into the database, only to reject legal operations.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 850877d1ea43104cc215353414b870c340acced2
User & Date: dan 2016-12-13 16:57:49
Context
2016-12-13
18:47
Convert sqlite3PagerGet() into a pointer-dispatched virtual method. This makes it about 25% faster. check-in: 7f88bb44 user: drh tags: trunk
16:57
Fix a problem causing SQLite to return false "foreign key violation" errors when there is a partial (i.e. WHERE constrained) UNIQUE index on the parent key columns. This bug did not cause SQLite to allow illegal data to be inserted into the database, only to reject legal operations. check-in: 850877d1 user: dan tags: trunk
2016-12-12
23:24
Add the --mmap option to the speedtest1 program and to the speed-check.sh script that is frequently used to run speedtest1. check-in: 1a636d5e user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/fkey.c.

221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
    assert( nCol>1 );
    aiCol = (int *)sqlite3DbMallocRawNN(pParse->db, nCol*sizeof(int));
    if( !aiCol ) return 1;
    *paiCol = aiCol;
  }

  for(pIdx=pParent->pIndex; pIdx; pIdx=pIdx->pNext){
    if( pIdx->nKeyCol==nCol && IsUniqueIndex(pIdx) ){ 
      /* pIdx is a UNIQUE index (or a PRIMARY KEY) and has the right number
      ** of columns. If each indexed column corresponds to a foreign key
      ** column of pFKey, then this index is a winner.  */

      if( zKey==0 ){
        /* If zKey is NULL, then this foreign key is implicitly mapped to 
        ** the PRIMARY KEY of table pParent. The PRIMARY KEY index may be 







|







221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
    assert( nCol>1 );
    aiCol = (int *)sqlite3DbMallocRawNN(pParse->db, nCol*sizeof(int));
    if( !aiCol ) return 1;
    *paiCol = aiCol;
  }

  for(pIdx=pParent->pIndex; pIdx; pIdx=pIdx->pNext){
    if( pIdx->nKeyCol==nCol && IsUniqueIndex(pIdx) && pIdx->pPartIdxWhere==0 ){ 
      /* pIdx is a UNIQUE index (or a PRIMARY KEY) and has the right number
      ** of columns. If each indexed column corresponds to a foreign key
      ** column of pFKey, then this index is a winner.  */

      if( zKey==0 ){
        /* If zKey is NULL, then this foreign key is implicitly mapped to 
        ** the PRIMARY KEY of table pParent. The PRIMARY KEY index may be 

Changes to test/fkey1.test.

11
12
13
14
15
16
17

18
19
20
21
22
23
24
...
180
181
182
183
184
185
186
187





















188
# This file implements regression tests for SQLite library.
#
# This file implements tests for foreign keys.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl


ifcapable {!foreignkey} {
  finish_test
  return
}

# Create a table and some data to work with.
................................................................................
  INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 1, 'A-2-1');
  INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (3, 2, 'A-3-2');
  INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (4, 3, 'A-4-3');
}
do_catchsql_test fkey1-5.4 {
  INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 3, 'A-2-3');
} {1 {FOREIGN KEY constraint failed}}






















finish_test







>







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
# This file implements regression tests for SQLite library.
#
# This file implements tests for foreign keys.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix fkey1

ifcapable {!foreignkey} {
  finish_test
  return
}

# Create a table and some data to work with.
................................................................................
  INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 1, 'A-2-1');
  INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (3, 2, 'A-3-2');
  INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (4, 3, 'A-4-3');
}
do_catchsql_test fkey1-5.4 {
  INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 3, 'A-2-3');
} {1 {FOREIGN KEY constraint failed}}

#-------------------------------------------------------------------------
# Check that foreign key processing is not fooled by partial indexes
# on the parent table.
#
do_execsql_test 6.0 {
  CREATE TABLE p1(x, y);
  CREATE UNIQUE INDEX p1x ON p1(x) WHERE y<2;
  INSERT INTO p1 VALUES(1, 1);
  CREATE TABLE c1(a REFERENCES p1(x));
}

do_catchsql_test 6.1 {
  INSERT INTO c1 VALUES(1);
} {1 {foreign key mismatch - "c1" referencing "p1"}}

do_execsql_test 6.2 {
  CREATE UNIQUE INDEX p1x2 ON p1(x);
  INSERT INTO c1 VALUES(1);
} {}


finish_test