What if you defined the foreign key with "ON DELETE CASCADE"? Dropping
the employer table will delete the employees.
Will
--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."
On 8/19/11 6:56 AM, "Boris Kolpackov" <bo...@codesynthesis.com> wrote:
>Hi,
>
>I am observing a "foreign key constraint failed" error that looks
>like an SQLite bug to me. I have tried the following using 3.7.7.1:
>
>First I create two tables:
>
>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;
>
>Now I want to drop them. If I do this:
>
>BEGIN TRANSACTION;
>DROP TABLE employer;
>DROP TABLE employee;
>COMMIT;
>
>I get "Error: foreign key constraint failed" when executing COMMIT.
>
>Here is a relevant quote from the SQLite Foreign Key Support[1] page:
>
>"If foreign key constraints are enabled when it is prepared, the DROP
>TABLE
>command performs an implicit DELETE to remove all rows from the table
>before
>dropping it. The implicit DELETE does not cause any SQL triggers to fire,
>but
>may invoke foreign key actions or constraint violations. If an immediate
>foreign key constraint is violated, the DROP TABLE statement fails and the
>table is not dropped. If a deferred foreign key constraint is violated,
>then
>an error is reported when the user attempts to commit the transaction if
>the
>foreign key constraint violations still exist at that point. Any "foreign
>key
>mismatch" errors encountered as part of an implicit DELETE are ignored."
>
>So seeing that my foreign key is deferred and at the end of the
>transaction
>all the violations have been resolved (there are no more rows in either
>table and there are no other tables -- this is a fresh database), I don't
>see why I am getting the error.
>
>If we change the order of DROPs, then everything works:
>
>BEGIN TRANSACTION;
>DROP TABLE employee;
>DROP TABLE employer;
>COMMIT;
>
>It also helps if we do explicit DELETEs before DROPs:
>
>BEGIN TRANSACTION;
>DELETE FROM employer;
>DELETE FROM employee;
>
>DROP TABLE employer;
>DROP TABLE employee;
>COMMIT;
>
>This, however, does not work:
>
>BEGIN TRANSACTION;
>DELETE FROM employer;
>DROP TABLE employer;
>
>DELETE FROM employee;
>DROP TABLE employee;
>COMMIT;
>
>In addition to the above error, this transaction also issues "Error: no
>such table: main.employer" after the second DELETE.
>
>Can someone confirm if this is a bug in SQLite? If so, I would also
>appreciate any suggestions for work-arounds. I know I can disable
>constraint checking, but in my case it is not easy since I am already
>in transaction.
>
>[1] http://www.sqlite.org/foreignkeys.html
>
>Thanks,
> 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
