SQLite

Check-in [2b3d9996a8]
Login

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

Overview
Comment:When updating a field that requires foreign key constraints be checked, ensure that the indexes and tables are consistent when the FK logic is run. Otherwise, it may detect the inconsistency and report database corruption.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2b3d9996a829c62fbaf7c92d50e44636340b07c6
User & Date: dan 2011-06-10 18:33:35.602
Context
2011-06-13
12:19
Use only unsigned values in the implementatin of LIKE and GLOB so that values won't overflow to negative when dealing with malformed UTF8. (check-in: 77f01578bb user: drh tags: trunk)
2011-06-10
18:33
When updating a field that requires foreign key constraints be checked, ensure that the indexes and tables are consistent when the FK logic is run. Otherwise, it may detect the inconsistency and report database corruption. (check-in: 2b3d9996a8 user: dan tags: trunk)
16:33
Fix minor problems with foreign key constraints where the parent table is the same as the child table. (check-in: 442d8d8bfe user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/update.c.
︙︙
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254

  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
  if( nIdx>0 ){
    aRegIdx = sqlite3DbMallocRaw(db, sizeof(Index*) * nIdx );
    if( aRegIdx==0 ) goto update_cleanup;
  }
  for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
    int reg;
    if( chngRowid ){
      reg = ++pParse->nMem;
    }else{
      reg = 0;
      for(i=0; i<pIdx->nColumn; i++){
        if( aXRef[pIdx->aiColumn[i]]>=0 ){
          reg = ++pParse->nMem;
          break;








|








240
241
242
243
244
245
246
247
248
249
250
251
252
253
254

  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
  if( nIdx>0 ){
    aRegIdx = sqlite3DbMallocRaw(db, sizeof(Index*) * nIdx );
    if( aRegIdx==0 ) goto update_cleanup;
  }
  for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
    int reg;
    if( hasFK || chngRowid ){
      reg = ++pParse->nMem;
    }else{
      reg = 0;
      for(i=0; i<pIdx->nColumn; i++){
        if( aXRef[pIdx->aiColumn[i]]>=0 ){
          reg = ++pParse->nMem;
          break;
︙︙
Changes to test/fkey3.test.
︙︙
150
151
152
153
154
155
156





























157

do_catchsql_test 3.5.4  { 
  INSERT INTO t7 VALUES('x', 450, 'x', NULL);
} {1 {foreign key constraint failed}}
do_catchsql_test 3.5.5  { 
  INSERT INTO t7 VALUES('x', 450, 'x', 451);
} {1 {foreign key constraint failed}}






























finish_test








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


150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186

do_catchsql_test 3.5.4  { 
  INSERT INTO t7 VALUES('x', 450, 'x', NULL);
} {1 {foreign key constraint failed}}
do_catchsql_test 3.5.5  { 
  INSERT INTO t7 VALUES('x', 450, 'x', 451);
} {1 {foreign key constraint failed}}


do_execsql_test 3.6.1 {
  CREATE TABLE t8(a, b, c, d, e, FOREIGN KEY(c, d) REFERENCES t8(a, b));
  CREATE UNIQUE INDEX t8i1 ON t8(a, b);
  CREATE UNIQUE INDEX t8i2 ON t8(c);
  INSERT INTO t8 VALUES(1, 1, 1, 1, 1);
}
do_catchsql_test 3.6.2 { 
  UPDATE t8 SET d = 2; 
} {1 {foreign key constraint failed}}
do_execsql_test 3.6.3 { UPDATE t8 SET d = 1; }
do_execsql_test 3.6.4 { UPDATE t8 SET e = 2; }

do_catchsql_test 3.6.5 {
  CREATE TABLE TestTable (
    id INTEGER PRIMARY KEY,
    name text,
    source_id integer not null,
    parent_id integer,

    foreign key(source_id, parent_id) references TestTable(source_id, id)
  );
  CREATE UNIQUE INDEX testindex on TestTable(source_id, id);
  PRAGMA foreign_keys=1;
  INSERT INTO TestTable VALUES (1, 'parent', 1, null);
  INSERT INTO TestTable VALUES (2, 'child', 1, 1);
  UPDATE TestTable SET parent_id=1000 where id=2;
} {1 {foreign key constraint failed}}

finish_test