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.
> 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
