On 8/19/11 10:44 AM, "Boris Kolpackov" <bo...@codesynthesis.com> wrote:
>Hi William,
>
>"Duquette, William H (318K)" <william.h.duque...@jpl.nasa.gov> writes:
>
>> On 8/19/11 10:18 AM, "Boris Kolpackov" <bo...@codesynthesis.com> wrote:
>>
>> There's something odd here. You have the FK constraints deferred, and
>> your code looks like this:
>>
>> BEGIN TRANSACTION;
>> DROP TABLE employer;
>> DROP TABLE employee;
>> COMMIT;
>>
>> According to the sqlite docs, dropping a table when FK constraints are
>> enabled does an implicit "DELETE FROM" first. That DELETE FROM is
>>causing
>> the constraint violations. But you have them deferred; they won't be
>> reported until the COMMIT, and only if the constraints are still
>>violated
>> at that time. But by then, you've also dropped the employee table, so
>>how
>> can there still be FK constraint violations?
>
>That's exactly what I have said in my original post ;-).
>
>
>> Am I missing something?
>
>If so, then that would be the two of us. Though I think this is a bug
>in SQLite.
I think it might be. (Richard? Anybody?)
Will
>
>
>> Are you sure you're dropping the tables in a transaction?
>
>The SQL code I included in my original email can be copy-n-pasted into
>the sqlite3 utility to verify this behavior. E.g., do:
>
>$ cat | sqlite3 /tmp/fresh.db
>
>Then copy-n-paste the following SQL (including the last blank line):
>
>--------------------------------
>PRAGMA foreign_keys=ON;
>
>BEGIN TRANSACTION;
>
>CREATE TABLE employer (name TEXT NOT NULL PRIMARY KEY);
>
>INSERT INTO employer VALUES('Simple Tech Ltd');
>
>CREATE TABLE employee (
> id INTEGER NOT NULL PRIMARY KEY,
> employer TEXT NOT NULL,
> FOREIGN KEY (employer) REFERENCES employer (name) DEFERRABLE INITIALLY
>DEFERRED);
>
>INSERT INTO employee VALUES(1, 'Simple Tech Ltd');
>
>COMMIT;
>
>BEGIN TRANSACTION;
>DROP TABLE employer;
>DROP TABLE employee;
>COMMIT;
>
>--------------------------------
>
>And you will get:
>
>Error: near line 21: foreign key constraint failed
>
>Boris
>--
>Boris Kolpackov, Code Synthesis
>http://codesynthesis.com/~boris/blog
>Compiler-based ORM system for C++
>http://codesynthesis.com/products/odb
>Open-source XML data binding for C++
>http://codesynthesis.com/products/xsd
>XML data binding for embedded systems
>http://codesynthesis.com/products/xsde
>
>_______________________________________________
>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
