Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Disallow using the implicit rowid column as the parent key of an FK constraint. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
a5f7e71f424900071a3925e53e9128c9 |
| User & Date: | dan 2009-09-23 13:59:17 |
Context
|
2009-09-23
| ||
| 14:45 | Change the version number to 3.6.19. Fix a couple of incorrect testcase() macros associated with the new IS and IS NOT operators in expr.c. (check-in: e3b73394 user: drh tags: trunk) | |
| 13:59 | Disallow using the implicit rowid column as the parent key of an FK constraint. (check-in: a5f7e71f user: dan tags: trunk) | |
| 13:39 | Add test cases for the IS and IS NOT operator. (check-in: 101ed58c user: drh tags: trunk) | |
Changes
Changes to src/fkey.c.
193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 |
** Otherwise, for a composite foreign key (more than one column), allocate
** space for the aiCol array (returned via output parameter *paiCol).
** Non-composite foreign keys do not require the aiCol array.
*/
if( nCol==1 ){
/* The FK maps to the IPK if any of the following are true:
**
** 1) The FK is explicitly mapped to "rowid", "oid" or "_rowid_", or
** 2) There is an explicit INTEGER PRIMARY KEY column and the FK is
** implicitly mapped to the primary key of table pParent, or
** 3) The FK is explicitly mapped to a column declared as INTEGER
** PRIMARY KEY.
*/
if( zKey && sqlite3IsRowid(zKey) ) return 0;
if( pParent->iPKey>=0 ){
if( !zKey ) return 0;
if( !sqlite3StrICmp(pParent->aCol[pParent->iPKey].zName, zKey) ) return 0;
}
}else if( paiCol ){
assert( nCol>1 );
aiCol = (int *)sqlite3DbMallocRaw(pParse->db, nCol*sizeof(int));
|
< | | | < |
193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 |
** Otherwise, for a composite foreign key (more than one column), allocate
** space for the aiCol array (returned via output parameter *paiCol).
** Non-composite foreign keys do not require the aiCol array.
*/
if( nCol==1 ){
/* The FK maps to the IPK if any of the following are true:
**
** 1) There is an INTEGER PRIMARY KEY column and the FK is implicitly
** mapped to the primary key of table pParent, or
** 2) The FK is explicitly mapped to a column declared as INTEGER
** PRIMARY KEY.
*/
if( pParent->iPKey>=0 ){
if( !zKey ) return 0;
if( !sqlite3StrICmp(pParent->aCol[pParent->iPKey].zName, zKey) ) return 0;
}
}else if( paiCol ){
assert( nCol>1 );
aiCol = (int *)sqlite3DbMallocRaw(pParse->db, nCol*sizeof(int));
|
Changes to test/fkey2.test.
77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 ... 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 ... 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 ... 554 555 556 557 558 559 560 561 562 563 564 565 566 567 |
PRAGMA foreign_keys = on; CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE t2(c REFERENCES t1(a) /D/ , d); CREATE TABLE t3(a PRIMARY KEY, b); CREATE TABLE t4(c REFERENCES t3 /D/, d); CREATE TABLE t5(a INTEGER PRIMARY KEY, b); CREATE TABLE t6(c REFERENCES t5(oid) /D/, d); CREATE TABLE t7(a, b INTEGER PRIMARY KEY); CREATE TABLE t8(c REFERENCES t7 /D/, d); CREATE TABLE t9(a REFERENCES nosuchtable, b); CREATE TABLE t10(a REFERENCES t9(c) /D/, b); } set FkeySimpleTests { ................................................................................ 1.12 "UPDATE t1 SET a = 2" {1 {foreign key constraint failed}} 1.13 "UPDATE t1 SET a = 1" {0 {}} 2.1 "INSERT INTO t4 VALUES(1, 3)" {1 {foreign key constraint failed}} 2.2 "INSERT INTO t3 VALUES(1, 2)" {0 {}} 2.3 "INSERT INTO t4 VALUES(1, 3)" {0 {}} 3.1 "INSERT INTO t6 VALUES(1, 3)" {1 {foreign key constraint failed}} 3.2 "INSERT INTO t5 VALUES(1, 2)" {0 {}} 3.3 "INSERT INTO t6 VALUES(1, 3)" {0 {}} 4.1 "INSERT INTO t8 VALUES(1, 3)" {1 {foreign key constraint failed}} 4.2 "INSERT INTO t7 VALUES(2, 1)" {0 {}} 4.3 "INSERT INTO t8 VALUES(1, 3)" {0 {}} 4.4 "INSERT INTO t8 VALUES(2, 4)" {1 {foreign key constraint failed}} 4.5 "INSERT INTO t8 VALUES(NULL, 4)" {0 {}} 4.6 "UPDATE t8 SET c=2 WHERE d=4" {1 {foreign key constraint failed}} 4.7 "UPDATE t8 SET c=1 WHERE d=4" {0 {}} ................................................................................ do_test fkey2-10.1.$tn { execsql $zSql catchsql { INSERT INTO c DEFAULT VALUES } } {1 {foreign key mismatch}} } # "rowid" cannot be used as part of a child key definition unless it # happens to be the name of an explicitly declared column. # do_test fkey2-10.2.1 { drop_all_tables catchsql { CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE t2(c, d, FOREIGN KEY(rowid) REFERENCES t1(a)); } ................................................................................ } {1 {unknown column "rowid" in foreign key definition}} do_test fkey2-10.2.2 { drop_all_tables catchsql { CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE t2(rowid, d, FOREIGN KEY(rowid) REFERENCES t1(a)); } } {0 {}} #------------------------------------------------------------------------- # The following tests, fkey2-11.*, test CASCADE actions. # drop_all_tables |
< < < < < < < | | > > > > > > > > > > > > > > > > > > |
77 78 79 80 81 82 83 84 85 86 87 88 89 90 ... 101 102 103 104 105 106 107 108 109 110 111 112 113 114 ... 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 ... 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 |
PRAGMA foreign_keys = on;
CREATE TABLE t1(a PRIMARY KEY, b);
CREATE TABLE t2(c REFERENCES t1(a) /D/ , d);
CREATE TABLE t3(a PRIMARY KEY, b);
CREATE TABLE t4(c REFERENCES t3 /D/, d);
CREATE TABLE t7(a, b INTEGER PRIMARY KEY);
CREATE TABLE t8(c REFERENCES t7 /D/, d);
CREATE TABLE t9(a REFERENCES nosuchtable, b);
CREATE TABLE t10(a REFERENCES t9(c) /D/, b);
}
set FkeySimpleTests {
................................................................................
1.12 "UPDATE t1 SET a = 2" {1 {foreign key constraint failed}}
1.13 "UPDATE t1 SET a = 1" {0 {}}
2.1 "INSERT INTO t4 VALUES(1, 3)" {1 {foreign key constraint failed}}
2.2 "INSERT INTO t3 VALUES(1, 2)" {0 {}}
2.3 "INSERT INTO t4 VALUES(1, 3)" {0 {}}
4.1 "INSERT INTO t8 VALUES(1, 3)" {1 {foreign key constraint failed}}
4.2 "INSERT INTO t7 VALUES(2, 1)" {0 {}}
4.3 "INSERT INTO t8 VALUES(1, 3)" {0 {}}
4.4 "INSERT INTO t8 VALUES(2, 4)" {1 {foreign key constraint failed}}
4.5 "INSERT INTO t8 VALUES(NULL, 4)" {0 {}}
4.6 "UPDATE t8 SET c=2 WHERE d=4" {1 {foreign key constraint failed}}
4.7 "UPDATE t8 SET c=1 WHERE d=4" {0 {}}
................................................................................
do_test fkey2-10.1.$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
catchsql {
CREATE TABLE t1(a PRIMARY KEY, b);
CREATE TABLE t2(c, d, FOREIGN KEY(rowid) REFERENCES t1(a));
}
................................................................................
} {1 {unknown column "rowid" in foreign key definition}}
do_test fkey2-10.2.2 {
drop_all_tables
catchsql {
CREATE TABLE t1(a PRIMARY KEY, b);
CREATE TABLE t2(rowid, d, FOREIGN KEY(rowid) REFERENCES t1(a));
}
} {0 {}}
do_test fkey2-10.2.1 {
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);
}
} {0 {}}
#-------------------------------------------------------------------------
# The following tests, fkey2-11.*, test CASCADE actions.
#
drop_all_tables
|