Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Use the affinity and collation sequence associated with the parent key when finding child table rows to apply a foreign key action to. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
9a4b7ec2928307e88783223903c842ac |
| User & Date: | dan 2009-09-29 16:38:59 |
Context
|
2009-09-30
| ||
| 04:28 | Change a couple of comments to use "SQLITE_MUTEX_OMIT" instead of OMIT_MUTEX. (check-in: b733e939 user: dan tags: trunk) | |
|
2009-09-29
| ||
| 16:38 | Use the affinity and collation sequence associated with the parent key when finding child table rows to apply a foreign key action to. (check-in: 9a4b7ec2 user: dan tags: trunk) | |
| 15:41 | Check that a unique index uses the default collation sequences for each column before using it as part of a foreign key constraint operation. (check-in: 64154174 user: dan tags: trunk) | |
Changes
Changes to src/fkey.c.
948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 |
assert( iFromCol>=0 );
tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid";
tFromCol.z = pFKey->pFrom->aCol[iFromCol].zName;
tToCol.n = sqlite3Strlen30(tToCol.z);
tFromCol.n = sqlite3Strlen30(tFromCol.z);
/* Create the expression "zFromCol = OLD.zToCol" */
pEq = sqlite3PExpr(pParse, TK_EQ,
sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol),
sqlite3PExpr(pParse, TK_DOT,
sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
, 0)
, 0);
pWhere = sqlite3ExprAnd(db, pWhere, pEq);
/* For ON UPDATE, construct the next term of the WHEN clause.
** The final WHEN clause will be like this:
**
** WHEN NOT(old.col1 IS new.col1 AND ... AND old.colN IS new.colN)
|
| > > > < | > |
948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 |
assert( iFromCol>=0 );
tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid";
tFromCol.z = pFKey->pFrom->aCol[iFromCol].zName;
tToCol.n = sqlite3Strlen30(tToCol.z);
tFromCol.n = sqlite3Strlen30(tFromCol.z);
/* Create the expression "OLD.zToCol = zFromCol". It is important
** that the "OLD.zToCol" term is on the LHS of the = operator, so
** that the affinity and collation sequence associated with the
** parent table are used for the comparison. */
pEq = sqlite3PExpr(pParse, TK_EQ,
sqlite3PExpr(pParse, TK_DOT,
sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
, 0),
sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol)
, 0);
pWhere = sqlite3ExprAnd(db, pWhere, pEq);
/* For ON UPDATE, construct the next term of the WHEN clause.
** The final WHEN clause will be like this:
**
** WHEN NOT(old.col1 IS new.col1 AND ... AND old.colN IS new.colN)
|
Changes to test/fkey2.test.
652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 ... 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 ... 765 766 767 768 769 770 771 772 773 774 775 776 777 778 |
}
} {{} A {} {} B {} 3 A 2 3 B 2}
#-------------------------------------------------------------------------
# The following tests, fkey2-10.*, test "foreign key mismatch" and
# other errors.
#
set tn 1
foreach zSql [list {
CREATE TABLE p(a PRIMARY KEY, b);
CREATE TABLE c(x REFERENCES p(c));
} {
CREATE TABLE c(x REFERENCES v(y));
CREATE VIEW v AS SELECT x AS y FROM c;
} {
................................................................................
CREATE TABLE c(x REFERENCES p);
} {
CREATE TABLE p(a COLLATE binary, b);
CREATE UNIQUE INDEX i ON p(a COLLATE nocase);
CREATE TABLE c(x REFERENCES p(a));
}] {
drop_all_tables
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.
................................................................................
} {1 {foreign key constraint failed}}
do_test fkey2-12.1.7 {
execsql {
INSERT INTO t1 VALUES(2, 'two');
COMMIT;
}
} {}
#-------------------------------------------------------------------------
# The following tests, fkey2-13.*, test that FK processing is performed
# when rows are REPLACEd.
#
drop_all_tables
do_test fkey2-13.1.1 {
|
| < | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 ... 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 ... 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 |
}
} {{} A {} {} B {} 3 A 2 3 B 2}
#-------------------------------------------------------------------------
# The following tests, fkey2-10.*, test "foreign key mismatch" and
# other errors.
#
set tn 0
foreach zSql [list {
CREATE TABLE p(a PRIMARY KEY, b);
CREATE TABLE c(x REFERENCES p(c));
} {
CREATE TABLE c(x REFERENCES v(y));
CREATE VIEW v AS SELECT x AS y FROM c;
} {
................................................................................
CREATE TABLE c(x REFERENCES p);
} {
CREATE TABLE p(a COLLATE binary, b);
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.
................................................................................
} {1 {foreign key constraint failed}}
do_test fkey2-12.1.7 {
execsql {
INSERT INTO t1 VALUES(2, 'two');
COMMIT;
}
} {}
drop_all_tables
do_test fkey2-12.2.1 {
execsql {
CREATE TABLE t1(x COLLATE NOCASE PRIMARY KEY);
CREATE TRIGGER tt1 AFTER DELETE ON t1
WHEN EXISTS ( SELECT 1 FROM t2 WHERE old.x = y )
BEGIN
INSERT INTO t1 VALUES(old.x);
END;
CREATE TABLE t2(y REFERENCES t1);
INSERT INTO t1 VALUES('A');
INSERT INTO t1 VALUES('B');
INSERT INTO t2 VALUES('a');
INSERT INTO t2 VALUES('b');
SELECT * FROM t1;
SELECT * FROM t2;
}
} {A B a b}
do_test fkey2-12.2.2 {
execsql { DELETE FROM t1 }
execsql {
SELECT * FROM t1;
SELECT * FROM t2;
}
} {A B a b}
do_test fkey2-12.2.3 {
execsql {
DROP TABLE t2;
CREATE TABLE t2(y REFERENCES t1 ON DELETE RESTRICT);
INSERT INTO t2 VALUES('a');
INSERT INTO t2 VALUES('b');
}
catchsql { DELETE FROM t1 }
} {1 {foreign key constraint failed}}
do_test fkey2-12.2.4 {
execsql {
SELECT * FROM t1;
SELECT * FROM t2;
}
} {A B a b}
#-------------------------------------------------------------------------
# The following tests, fkey2-13.*, test that FK processing is performed
# when rows are REPLACEd.
#
drop_all_tables
do_test fkey2-13.1.1 {
|