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
