In that case, we get the desired behavior. Both calls to sqlite3_step return SQLITE_DONE because it is a deferred constraint. When you execute the "COMMIT;", it then returns SQLITE_CONSTRAINT because of the constraint violation.
To summarize, to reproduce this problem, the statements must not be enclosed in a BEGIN-COMMIT block. To be very explicit, here is the code that I used to test this: sqlite3* pDatabase; sqlite3_open( ":memory:", &pDatabase ); sqlite3_exec( pDatabase, "PRAGMA foreign_keys =on;", NULL, NULL, NULL ); sqlite3_exec( pDatabase, "CREATE TABLE t1(a PRIMARY KEY, b);", NULL, NULL, NULL ); sqlite3_exec( pDatabase, "CREATE TABLE t2(c REFERENCES t1(a) DEFERRABLE INITIALLY DEFERRED, d);", NULL, NULL, NULL ); sqlite3_exec( pDatabase, "INSERT INTO t1 VALUES(1, 2);", NULL, NULL, NULL ); // Does not violate constraint sqlite3_exec( pDatabase, "INSERT INTO t2 VALUES(1, 3);", NULL, NULL, NULL ); sqlite3_exec( pDatabase, "BEGIN;", NULL, NULL, NULL ); sqlite3_stmt* pStatement1; sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(2, 4);", -1, &pStatement1, NULL ); // Returns SQLITE_DONE because constraint is deferred sqlite3_step( pStatement1 ); sqlite3_stmt* pStatement2; sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(3, 6);", -1, &pStatement2, NULL); // Returns SQLITE_DONE because constraint is deferred sqlite3_step( pStatement2 ); // Returns SQLITE_CONSTRAINT because both statements violate the constraint sqlite3_exec( pDatabase, "COMMIT;", NULL, NULL, NULL ); sqlite3_finalize( pStatement2 ); sqlite3_finalize( pStatement1 ); sqlite3_close( pDatabase ); Steve -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jan Sent: Friday, January 21, 2011 2:39 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Deferred foreign key constraint bug? What happens with a transaction around your statements? Am 21.01.2011 19:38, schrieb Steve Campbell: > A lingering statement handle can prevent a deferred foreign key constraint > from being enforced. Here is an example: > > sqlite3_open( ":memory:",&pDatabase ); > sqlite3_exec( pDatabase, "PRAGMA foreign_keys =on;", NULL, NULL, NULL ); > sqlite3_exec( pDatabase, "CREATE TABLE t1(a PRIMARY KEY, b);", NULL, NULL, > NULL ); > sqlite3_exec( pDatabase, "CREATE TABLE t2(c REFERENCES t1(a) DEFERRABLE > INITIALLY DEFERRED, d);", NULL, NULL, NULL ); > sqlite3_exec( pDatabase, "INSERT INTO t1 VALUES(1, 2);", NULL, NULL, NULL ); > > // Does not violate constraint; will return SQLITE_OK > sqlite3_exec( pDatabase, "INSERT INTO t2 VALUES(1, 3);", NULL, NULL, NULL ) > > sqlite3_stmt* pStatement1; > sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(2, 4);", > -1,&pStatement1, NULL); > > // Does violate constraint > // Will return SQLITE_CONSTRAINT > sqlite3_step( pStatement1 ); > > sqlite3_stmt* pStatement2; > sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(3, 6);", > -1,&pStatement2, NULL); > > // Note that pStatement1 has not been finalized yet > // Does violate constraint > // Should return SQLITE_CONSTRAINT, but it will return SQLITE_OK instead; > this is the malfunction > sqlite3_step( pStatement2 ); > > sqlite3_finalize( pStatement2 ); > sqlite3_finalize( pStatement1 ); > sqlite3_close( pDatabase ); > > The last call to sqlite3_step should return SQLITE_CONSTRAINT, but it will > return SQLITE_OK instead. > > If you change the code to finalize statement1 first, then the last call to > sqlite3_step will return SQLITE_CONSTRAINT. Also, if you declare the foreign > key constraint to be immediate instead of deferred, the last call to > sqlite3_step will return SQLITE_CONSTRAINT. > > To summarize, we can prevent foreign keys from being properly enforced if we > declare the constraints to be deferred and we leave lingering statement > handles open. > > Is this a bug? > > Much appreciated, > Steve Campbell > > --------------------------------------------------------------------- > This transmission (including any attachments) may contain confidential > information, privileged material (including material protected by the > solicitor-client or other applicable privileges), or constitute non-public > information. Any use of this information by anyone other than the intended > recipient is prohibited. If you have received this transmission in error, > please immediately reply to the sender and delete this information from your > system. Use, dissemination, distribution, or reproduction of this > transmission by unintended recipients is not authorized and may be unlawful. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --------------------------------------------------------------------- This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
