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
