Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Enhance the error message for "foreign key mismatch" to include the names of the child and parent tables. Begin adding test cases for PRAGMA foreign_key_check. Make sure PRAGMA foreign_key_check gets all necessary table locks. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | foreign-key-check |
| Files: | files | file ages | folders |
| SHA1: |
0f9963526c299757ff95c153fb05a67c |
| User & Date: | drh 2012-12-17 20:40:39 |
Context
|
2012-12-17
| ||
| 20:57 | Add to the foreign_key_check pragma an extra output column "parent" that contains the name of the parent table for the constraint that failed. check-in: 97f7f737 user: drh tags: foreign-key-check | |
| 20:40 | Enhance the error message for "foreign key mismatch" to include the names of the child and parent tables. Begin adding test cases for PRAGMA foreign_key_check. Make sure PRAGMA foreign_key_check gets all necessary table locks. check-in: 0f996352 user: drh tags: foreign-key-check | |
| 18:43 | "PRAGMA foreign_key_check" with no argument checks the foreign keys on all tables. check-in: 57d47423 user: drh tags: foreign-key-check | |
Changes
Changes to src/fkey.c.
271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 |
if( i==nCol ) break; /* pIdx is usable */
}
}
}
if( !pIdx ){
if( !pParse->disableTriggers ){
sqlite3ErrorMsg(pParse, "foreign key mismatch");
}
sqlite3DbFree(pParse->db, aiCol);
return 1;
}
*ppIdx = pIdx;
return 0;
|
| > > |
271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 |
if( i==nCol ) break; /* pIdx is usable */
}
}
}
if( !pIdx ){
if( !pParse->disableTriggers ){
sqlite3ErrorMsg(pParse,
"foreign key mismatch - \"%w\" referencing \"%w\"",
pFKey->pFrom->zName, pFKey->zTo);
}
sqlite3DbFree(pParse->db, aiCol);
return 1;
}
*ppIdx = pIdx;
return 0;
|
Changes to src/pragma.c.
1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 |
if( zRight ){
pTab = sqlite3LocateTable(pParse, 0, zRight, zDb);
k = 0;
}else{
pTab = (Table*)sqliteHashData(k);
k = sqliteHashNext(k);
}
if( pTab->pFKey==0 ) continue;
if( pTab->nCol+regRow>pParse->nMem ) pParse->nMem = pTab->nCol + regRow;
sqlite3OpenTable(pParse, 0, iDb, pTab, OP_OpenRead);
sqlite3VdbeAddOp4(v, OP_String8, 0, regResult, 0, pTab->zName,
P4_TRANSIENT);
for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
if( pParent==0 ) break;
pIdx = 0;
x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, 0);
if( x==0 ){
if( pIdx==0 ){
sqlite3OpenTable(pParse, i, iDb, pParent, OP_OpenRead);
}else{
KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
sqlite3VdbeAddOp3(v, OP_OpenRead, i, pIdx->tnum, iDb);
|
| > > |
1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 |
if( zRight ){
pTab = sqlite3LocateTable(pParse, 0, zRight, zDb);
k = 0;
}else{
pTab = (Table*)sqliteHashData(k);
k = sqliteHashNext(k);
}
if( pTab==0 || pTab->pFKey==0 ) continue;
sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
if( pTab->nCol+regRow>pParse->nMem ) pParse->nMem = pTab->nCol + regRow;
sqlite3OpenTable(pParse, 0, iDb, pTab, OP_OpenRead);
sqlite3VdbeAddOp4(v, OP_String8, 0, regResult, 0, pTab->zName,
P4_TRANSIENT);
for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
if( pParent==0 ) break;
pIdx = 0;
sqlite3TableLock(pParse, iDb, pParent->tnum, 0, pParent->zName);
x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, 0);
if( x==0 ){
if( pIdx==0 ){
sqlite3OpenTable(pParse, i, iDb, pParent, OP_OpenRead);
}else{
KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
sqlite3VdbeAddOp3(v, OP_OpenRead, i, pIdx->tnum, iDb);
|
Changes to test/e_fkey.test.
623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 ... 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 ... 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 ... 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 .... 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 .... 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 |
}
} {}
proc test_efkey_57 {tn isError sql} {
catchsql { DROP TABLE t1 }
execsql $sql
do_test e_fkey-18.$tn {
catchsql { INSERT INTO t2 VALUES(NULL) }
} [lindex {{0 {}} {1 {foreign key mismatch}}} $isError]
}
test_efkey_57 2 0 { CREATE TABLE t1(x PRIMARY KEY) }
test_efkey_57 3 0 { CREATE TABLE t1(x UNIQUE) }
test_efkey_57 4 0 { CREATE TABLE t1(x); CREATE UNIQUE INDEX t1i ON t1(x) }
test_efkey_57 5 1 {
CREATE TABLE t1(x);
CREATE UNIQUE INDEX t1i ON t1(x COLLATE nocase);
................................................................................
INSERT INTO child1 VALUES('xxx', 1);
INSERT INTO child2 VALUES('xxx', 2);
INSERT INTO child3 VALUES(3, 4);
}
} {}
do_test e_fkey-19.2 {
catchsql { INSERT INTO child4 VALUES('xxx', 5) }
} {1 {foreign key mismatch}}
do_test e_fkey-19.3 {
catchsql { INSERT INTO child5 VALUES('xxx', 6) }
} {1 {foreign key mismatch}}
do_test e_fkey-19.4 {
catchsql { INSERT INTO child6 VALUES(2, 3) }
} {1 {foreign key mismatch}}
do_test e_fkey-19.5 {
catchsql { INSERT INTO child7 VALUES(3) }
} {1 {foreign key mismatch}}
#-------------------------------------------------------------------------
# Test errors in the database schema that are detected while preparing
# DML statements. The error text for these messages always matches
# either "foreign key mismatch" or "no such table*" (using [string match]).
#
# EVIDENCE-OF: R-45488-08504 If the database schema contains foreign key
................................................................................
CREATE TABLE p7(a, b, PRIMARY KEY(a, b));
CREATE TABLE c7(c, d REFERENCES p7);
}
} {}
foreach {tn tbl ptbl err} {
2 c1 {} "no such table: main.nosuchtable"
3 c2 p2 "foreign key mismatch"
4 c3 p3 "foreign key mismatch"
5 c4 p4 "foreign key mismatch"
6 c5 p5 "foreign key mismatch"
7 c6 p6 "foreign key mismatch"
8 c7 p7 "foreign key mismatch"
} {
do_test e_fkey-20.$tn.1 {
catchsql "INSERT INTO $tbl VALUES('a', 'b')"
} [list 1 $err]
do_test e_fkey-20.$tn.2 {
catchsql "UPDATE $tbl SET c = ?, d = ?"
} [list 1 $err]
................................................................................
execsql {
INSERT INTO parent2 VALUES('I', 'II');
INSERT INTO child8 VALUES('I', 'II');
}
} {}
do_test e_fkey-21.3 {
catchsql { INSERT INTO child9 VALUES('I') }
} {1 {foreign key mismatch}}
do_test e_fkey-21.4 {
catchsql { INSERT INTO child9 VALUES('II') }
} {1 {foreign key mismatch}}
do_test e_fkey-21.5 {
catchsql { INSERT INTO child9 VALUES(NULL) }
} {1 {foreign key mismatch}}
do_test e_fkey-21.6 {
catchsql { INSERT INTO child10 VALUES('I', 'II', 'III') }
} {1 {foreign key mismatch}}
do_test e_fkey-21.7 {
catchsql { INSERT INTO child10 VALUES(1, 2, 3) }
} {1 {foreign key mismatch}}
do_test e_fkey-21.8 {
catchsql { INSERT INTO child10 VALUES(NULL, NULL, NULL) }
} {1 {foreign key mismatch}}
#-------------------------------------------------------------------------
# Test errors that are reported when creating the child table.
# Specifically:
#
# * different number of child and parent key columns, and
# * child columns that do not exist.
................................................................................
do_test e_fkey-28.8 {
drop_all_tables
execsql {
CREATE TABLE p(x PRIMARY KEY);
CREATE TABLE c(a, b, FOREIGN KEY(a,b) REFERENCES p);
}
catchsql {DELETE FROM p}
} {1 {foreign key mismatch}}
do_test e_fkey-28.9 {
drop_all_tables
execsql {
CREATE TABLE p(x, y, PRIMARY KEY(x,y));
CREATE TABLE c(a REFERENCES p);
}
catchsql {DELETE FROM p}
} {1 {foreign key mismatch}}
#-------------------------------------------------------------------------
# EVIDENCE-OF: R-24676-09859
#
# Test the example schema in the "Composite Foreign Key Constraints"
# section.
................................................................................
SELECT * FROM c3;
ROLLBACK;
}
} {{} 2}
do_test e_fkey-60.4 {
execsql { CREATE TABLE nosuchtable(x PRIMARY KEY) }
catchsql { DELETE FROM p }
} {1 {foreign key mismatch}}
do_test e_fkey-60.5 {
execsql { DROP TABLE c1 }
catchsql { DELETE FROM p }
} {1 {foreign key mismatch}}
do_test e_fkey-60.6 {
execsql { DROP TABLE c2 }
execsql { DELETE FROM p }
} {}
#-------------------------------------------------------------------------
# Test that the special behaviours of ALTER and DROP TABLE are only
|
| > | | | | | | | | | | | | | | | | | | | | |
623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 ... 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 ... 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 ... 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 .... 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 .... 2726 2727 2728 2729 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 |
}
} {}
proc test_efkey_57 {tn isError sql} {
catchsql { DROP TABLE t1 }
execsql $sql
do_test e_fkey-18.$tn {
catchsql { INSERT INTO t2 VALUES(NULL) }
} [lindex {{0 {}} {/1 {foreign key mismatch - ".*" referencing ".*"}/}} \
$isError]
}
test_efkey_57 2 0 { CREATE TABLE t1(x PRIMARY KEY) }
test_efkey_57 3 0 { CREATE TABLE t1(x UNIQUE) }
test_efkey_57 4 0 { CREATE TABLE t1(x); CREATE UNIQUE INDEX t1i ON t1(x) }
test_efkey_57 5 1 {
CREATE TABLE t1(x);
CREATE UNIQUE INDEX t1i ON t1(x COLLATE nocase);
................................................................................
INSERT INTO child1 VALUES('xxx', 1);
INSERT INTO child2 VALUES('xxx', 2);
INSERT INTO child3 VALUES(3, 4);
}
} {}
do_test e_fkey-19.2 {
catchsql { INSERT INTO child4 VALUES('xxx', 5) }
} {1 {foreign key mismatch - "child4" referencing "parent"}}
do_test e_fkey-19.3 {
catchsql { INSERT INTO child5 VALUES('xxx', 6) }
} {1 {foreign key mismatch - "child5" referencing "parent"}}
do_test e_fkey-19.4 {
catchsql { INSERT INTO child6 VALUES(2, 3) }
} {1 {foreign key mismatch - "child6" referencing "parent"}}
do_test e_fkey-19.5 {
catchsql { INSERT INTO child7 VALUES(3) }
} {1 {foreign key mismatch - "child7" referencing "parent"}}
#-------------------------------------------------------------------------
# Test errors in the database schema that are detected while preparing
# DML statements. The error text for these messages always matches
# either "foreign key mismatch" or "no such table*" (using [string match]).
#
# EVIDENCE-OF: R-45488-08504 If the database schema contains foreign key
................................................................................
CREATE TABLE p7(a, b, PRIMARY KEY(a, b));
CREATE TABLE c7(c, d REFERENCES p7);
}
} {}
foreach {tn tbl ptbl err} {
2 c1 {} "no such table: main.nosuchtable"
3 c2 p2 "foreign key mismatch - \"c2\" referencing \"p2\""
4 c3 p3 "foreign key mismatch - \"c3\" referencing \"p3\""
5 c4 p4 "foreign key mismatch - \"c4\" referencing \"p4\""
6 c5 p5 "foreign key mismatch - \"c5\" referencing \"p5\""
7 c6 p6 "foreign key mismatch - \"c6\" referencing \"p6\""
8 c7 p7 "foreign key mismatch - \"c7\" referencing \"p7\""
} {
do_test e_fkey-20.$tn.1 {
catchsql "INSERT INTO $tbl VALUES('a', 'b')"
} [list 1 $err]
do_test e_fkey-20.$tn.2 {
catchsql "UPDATE $tbl SET c = ?, d = ?"
} [list 1 $err]
................................................................................
execsql {
INSERT INTO parent2 VALUES('I', 'II');
INSERT INTO child8 VALUES('I', 'II');
}
} {}
do_test e_fkey-21.3 {
catchsql { INSERT INTO child9 VALUES('I') }
} {1 {foreign key mismatch - "child9" referencing "parent2"}}
do_test e_fkey-21.4 {
catchsql { INSERT INTO child9 VALUES('II') }
} {1 {foreign key mismatch - "child9" referencing "parent2"}}
do_test e_fkey-21.5 {
catchsql { INSERT INTO child9 VALUES(NULL) }
} {1 {foreign key mismatch - "child9" referencing "parent2"}}
do_test e_fkey-21.6 {
catchsql { INSERT INTO child10 VALUES('I', 'II', 'III') }
} {1 {foreign key mismatch - "child10" referencing "parent2"}}
do_test e_fkey-21.7 {
catchsql { INSERT INTO child10 VALUES(1, 2, 3) }
} {1 {foreign key mismatch - "child10" referencing "parent2"}}
do_test e_fkey-21.8 {
catchsql { INSERT INTO child10 VALUES(NULL, NULL, NULL) }
} {1 {foreign key mismatch - "child10" referencing "parent2"}}
#-------------------------------------------------------------------------
# Test errors that are reported when creating the child table.
# Specifically:
#
# * different number of child and parent key columns, and
# * child columns that do not exist.
................................................................................
do_test e_fkey-28.8 {
drop_all_tables
execsql {
CREATE TABLE p(x PRIMARY KEY);
CREATE TABLE c(a, b, FOREIGN KEY(a,b) REFERENCES p);
}
catchsql {DELETE FROM p}
} {1 {foreign key mismatch - "c" referencing "p"}}
do_test e_fkey-28.9 {
drop_all_tables
execsql {
CREATE TABLE p(x, y, PRIMARY KEY(x,y));
CREATE TABLE c(a REFERENCES p);
}
catchsql {DELETE FROM p}
} {1 {foreign key mismatch - "c" referencing "p"}}
#-------------------------------------------------------------------------
# EVIDENCE-OF: R-24676-09859
#
# Test the example schema in the "Composite Foreign Key Constraints"
# section.
................................................................................
SELECT * FROM c3;
ROLLBACK;
}
} {{} 2}
do_test e_fkey-60.4 {
execsql { CREATE TABLE nosuchtable(x PRIMARY KEY) }
catchsql { DELETE FROM p }
} {1 {foreign key mismatch - "c2" referencing "p"}}
do_test e_fkey-60.5 {
execsql { DROP TABLE c1 }
catchsql { DELETE FROM p }
} {1 {foreign key mismatch - "c2" referencing "p"}}
do_test e_fkey-60.6 {
execsql { DROP TABLE c2 }
execsql { DELETE FROM p }
} {}
#-------------------------------------------------------------------------
# Test that the special behaviours of ALTER and DROP TABLE are only
|
Changes to test/fkey2.test.
135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 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 ... 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 ... 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 .... 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 |
4.13 "UPDATE t7 SET b = 1" {0 {}}
4.14 "INSERT INTO t8 VALUES('a', 'b')" {1 {foreign key constraint failed}}
4.15 "UPDATE t7 SET b = 5" {1 {foreign key constraint failed}}
4.16 "UPDATE t7 SET rowid = 5" {1 {foreign key constraint failed}}
4.17 "UPDATE t7 SET a = 10" {0 {}}
5.1 "INSERT INTO t9 VALUES(1, 3)" {1 {no such table: main.nosuchtable}}
5.2 "INSERT INTO t10 VALUES(1, 3)" {1 {foreign key mismatch}}
}
do_test fkey2-1.1.0 {
execsql [string map {/D/ {}} $FkeySimpleSchema]
} {}
foreach {tn zSql res} $FkeySimpleTests {
do_test fkey2-1.1.$tn { catchsql $zSql } $res
}
drop_all_tables
do_test fkey2-1.2.0 {
execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema]
} {}
foreach {tn zSql res} $FkeySimpleTests {
do_test fkey2-1.2.$tn { catchsql $zSql } $res
}
drop_all_tables
do_test fkey2-1.3.0 {
execsql [string map {/D/ {}} $FkeySimpleSchema]
execsql { PRAGMA count_changes = 1 }
} {}
foreach {tn zSql res} $FkeySimpleTests {
if {$res == "0 {}"} { set res {0 1} }
do_test fkey2-1.3.$tn { catchsql $zSql } $res
}
execsql { PRAGMA count_changes = 0 }
drop_all_tables
do_test fkey2-1.4.0 {
execsql [string map {/D/ {}} $FkeySimpleSchema]
execsql { PRAGMA count_changes = 1 }
................................................................................
CREATE UNIQUE INDEX i ON p(a COLLATE nocase);
CREATE TABLE c(x REFERENCES p(a));
}] {
drop_all_tables
do_test fkey2-10.1.[incr tn] {
execsql $zSql
catchsql { INSERT INTO c DEFAULT VALUES }
} {1 {foreign key mismatch}}
}
# "rowid" cannot be used as part of a child or parent key definition
# unless it happens to be the name of an explicitly declared column.
#
do_test fkey2-10.2.1 {
drop_all_tables
................................................................................
drop_all_tables
catchsql {
CREATE TABLE t1(a, b);
CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1);
INSERT INTO t2 VALUES(1, 1);
}
} {1 {foreign key mismatch}}
do_test fkey2-10.2.2 {
drop_all_tables
catchsql {
CREATE TABLE t1(rowid PRIMARY KEY, b);
CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
INSERT INTO t1(rowid, b) VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
................................................................................
} {}
do_test fkey-2.14.3.8 {
execsql {
CREATE TABLE pp(x, y, PRIMARY KEY(x, y));
CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
}
catchsql { INSERT INTO cc VALUES(1, 2) }
} {1 {foreign key mismatch}}
do_test fkey-2.14.3.9 {
execsql { DROP TABLE cc }
} {}
do_test fkey-2.14.3.10 {
execsql {
CREATE TABLE cc(a, b,
FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED
|
| > | > > > > > > > > > > > > > > > > > > | | | |
135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 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 187 188 189 190 191 192 193 ... 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 ... 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 .... 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 |
4.13 "UPDATE t7 SET b = 1" {0 {}}
4.14 "INSERT INTO t8 VALUES('a', 'b')" {1 {foreign key constraint failed}}
4.15 "UPDATE t7 SET b = 5" {1 {foreign key constraint failed}}
4.16 "UPDATE t7 SET rowid = 5" {1 {foreign key constraint failed}}
4.17 "UPDATE t7 SET a = 10" {0 {}}
5.1 "INSERT INTO t9 VALUES(1, 3)" {1 {no such table: main.nosuchtable}}
5.2 "INSERT INTO t10 VALUES(1, 3)"
{1 {foreign key mismatch - "t10" referencing "t9"}}
}
do_test fkey2-1.1.0 {
execsql [string map {/D/ {}} $FkeySimpleSchema]
} {}
foreach {tn zSql res} $FkeySimpleTests {
do_test fkey2-1.1.$tn.1 { catchsql $zSql } $res
do_test fkey2-1.1.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
do_test fkey2-1.1.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
do_test fkey2-1.1.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
do_test fkey2-1.1.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
do_test fkey2-1.1.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
do_test fkey2-1.1.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
}
drop_all_tables
do_test fkey2-1.2.0 {
execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema]
} {}
foreach {tn zSql res} $FkeySimpleTests {
do_test fkey2-1.2.$tn { catchsql $zSql } $res
do_test fkey2-1.2.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
do_test fkey2-1.2.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
do_test fkey2-1.2.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
do_test fkey2-1.2.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
do_test fkey2-1.2.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
do_test fkey2-1.2.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
}
drop_all_tables
do_test fkey2-1.3.0 {
execsql [string map {/D/ {}} $FkeySimpleSchema]
execsql { PRAGMA count_changes = 1 }
} {}
foreach {tn zSql res} $FkeySimpleTests {
if {$res == "0 {}"} { set res {0 1} }
do_test fkey2-1.3.$tn { catchsql $zSql } $res
do_test fkey2-1.3.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
do_test fkey2-1.3.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
do_test fkey2-1.3.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
do_test fkey2-1.3.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
do_test fkey2-1.3.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
do_test fkey2-1.3.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
}
execsql { PRAGMA count_changes = 0 }
drop_all_tables
do_test fkey2-1.4.0 {
execsql [string map {/D/ {}} $FkeySimpleSchema]
execsql { PRAGMA count_changes = 1 }
................................................................................
CREATE UNIQUE INDEX i ON p(a COLLATE nocase);
CREATE TABLE c(x REFERENCES p(a));
}] {
drop_all_tables
do_test fkey2-10.1.[incr tn] {
execsql $zSql
catchsql { INSERT INTO c DEFAULT VALUES }
} {/1 {foreign key mismatch - "c" referencing "."}/}
}
# "rowid" cannot be used as part of a child or parent key definition
# unless it happens to be the name of an explicitly declared column.
#
do_test fkey2-10.2.1 {
drop_all_tables
................................................................................
drop_all_tables
catchsql {
CREATE TABLE t1(a, b);
CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1);
INSERT INTO t2 VALUES(1, 1);
}
} {1 {foreign key mismatch - "t2" referencing "t1"}}
do_test fkey2-10.2.2 {
drop_all_tables
catchsql {
CREATE TABLE t1(rowid PRIMARY KEY, b);
CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
INSERT INTO t1(rowid, b) VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
................................................................................
} {}
do_test fkey-2.14.3.8 {
execsql {
CREATE TABLE pp(x, y, PRIMARY KEY(x, y));
CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
}
catchsql { INSERT INTO cc VALUES(1, 2) }
} {1 {foreign key mismatch - "cc" referencing "pp"}}
do_test fkey-2.14.3.9 {
execsql { DROP TABLE cc }
} {}
do_test fkey-2.14.3.10 {
execsql {
CREATE TABLE cc(a, b,
FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED
|