Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Correct handling of compound foreign key constraints that include the integer primary key as one of the columns. Ticket [ce7c133ea6cc9ccdc1] |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | 53902f7d4a46aa70ecc5bf180a01ff888d52686a |
| User & Date: | drh 2010-07-29 01:50:39 |
References
|
2010-07-29
| ||
| 01:55 | • Ticket [ce7c133e] Foreign key constraint fails when it should succeed. status still Open with 4 other changes artifact: 6fb11fef user: drh | |
Context
|
2010-07-29
| ||
| 10:07 | Change the profile timer units back to nanoseconds and update the sqlite3_profile() documentation. Ticket [c43940c49b74c70a69] check-in: 7783b98a user: drh tags: trunk | |
| 01:50 | Correct handling of compound foreign key constraints that include the integer primary key as one of the columns. Ticket [ce7c133ea6cc9ccdc1] check-in: 53902f7d user: drh tags: trunk | |
|
2010-07-28
| ||
| 19:17 | Get SQLITE_OMIT_VIRTUALTABLE working again after being broken by recent changes. check-in: 33b1e862 user: drh tags: trunk | |
Changes
Changes to src/fkey.c.
496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 |
pLeft = sqlite3Expr(db, TK_REGISTER, 0);
if( pLeft ){
/* Set the collation sequence and affinity of the LHS of each TK_EQ
** expression to the parent key column defaults. */
if( pIdx ){
Column *pCol;
iCol = pIdx->aiColumn[i];
pCol = &pIdx->pTable->aCol[iCol];
pLeft->iTable = regData+iCol+1;
pLeft->affinity = pCol->affinity;
pLeft->pColl = sqlite3LocateCollSeq(pParse, pCol->zColl);
}else{
pLeft->iTable = regData;
pLeft->affinity = SQLITE_AFF_INTEGER;
}
|
| > |
496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 |
pLeft = sqlite3Expr(db, TK_REGISTER, 0);
if( pLeft ){
/* Set the collation sequence and affinity of the LHS of each TK_EQ
** expression to the parent key column defaults. */
if( pIdx ){
Column *pCol;
iCol = pIdx->aiColumn[i];
pCol = &pTab->aCol[iCol];
if( pTab->iPKey==iCol ) iCol = -1;
pLeft->iTable = regData+iCol+1;
pLeft->affinity = pCol->affinity;
pLeft->pColl = sqlite3LocateCollSeq(pParse, pCol->zColl);
}else{
pLeft->iTable = regData;
pLeft->affinity = SQLITE_AFF_INTEGER;
}
|
Changes to test/fkey2.test.
1932 1933 1934 1935 1936 1937 1938 1939 1940 |
} {1 {foreign key constraint failed}}
do_test fkey2-dd08e5.1.6 {
catchsql {
UPDATE tdd08 SET a=a+1;
}
} {1 {foreign key constraint failed}}
finish_test
|
> > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 |
} {1 {foreign key constraint failed}}
do_test fkey2-dd08e5.1.6 {
catchsql {
UPDATE tdd08 SET a=a+1;
}
} {1 {foreign key constraint failed}}
#-------------------------------------------------------------------------
# Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba
# fixed.
#
do_test fkey2-ce7c13.1.1 {
execsql {
CREATE TABLE tce71(a INTEGER PRIMARY KEY, b);
CREATE UNIQUE INDEX ice71 ON tce71(a,b);
INSERT INTO tce71 VALUES(100,200);
CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b));
INSERT INTO tce72 VALUES(300,100,200);
UPDATE tce71 set b = 200 where a = 100;
SELECT * FROM tce71, tce72;
}
} {100 200 300 100 200}
do_test fkey2-ce7c13.1.2 {
catchsql {
UPDATE tce71 set b = 201 where a = 100;
}
} {1 {foreign key constraint failed}}
do_test fkey2-ce7c13.1.3 {
catchsql {
UPDATE tce71 set a = 101 where a = 100;
}
} {1 {foreign key constraint failed}}
do_test fkey2-ce7c13.1.4 {
execsql {
CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b));
INSERT INTO tce73 VALUES(100,200);
CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b));
INSERT INTO tce74 VALUES(300,100,200);
UPDATE tce73 set b = 200 where a = 100;
SELECT * FROM tce73, tce74;
}
} {100 200 300 100 200}
do_test fkey2-ce7c13.1.5 {
catchsql {
UPDATE tce73 set b = 201 where a = 100;
}
} {1 {foreign key constraint failed}}
do_test fkey2-ce7c13.1.6 {
catchsql {
UPDATE tce73 set a = 101 where a = 100;
}
} {1 {foreign key constraint failed}}
finish_test
|