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
