On 13 May 2013 16:52, Simon Slavin <slav...@bigfraud.org> wrote: > > On 13 May 2013, at 3:54pm, Григорий Григоренко <grigore...@mail.ru> wrote: > >> sample database is: >> >> PRAGMA FOREIGN_KEYS=1; >> CREATE TABLE cat(id INTEGER PRIMARY KEY, name); >> INSERT INTO cat VALUES (1, 'Alice'); >> CREATE TABLE owner(pet INTEGER REFERENCES cat(id)); >> INSERT INTO owner VALUES(1); >> >> This script fails to drop tables with 'foreign key constraint failed': >> >> SAVEPOINT edit; >> PRAGMA FOREIGN_KEYS=0; >> DROP TABLE cat; >> DROP TABLE owner; >> RELEASE edit; >> PRAGMA FOREIGN_KEYS=1; > > You are DROPping your tables in the wrong order. The 'owner' table refers to > the 'cat' table. If you DROP 'cat' you would be left with an 'owner' table > which refers to a table which doesn't exist. Swap the order of the DROPs and > your database will not risk that kind of disaster. > >> This script works OK: >> >> PRAGMA FOREIGN_KEYS=0; >> DROP TABLE cat; >> DROP TABLE owner; >> PRAGMA FOREIGN_KEYS=1; > > Your problem with the one with SAVEPOINT is that SQLite doesn't know when > you're going to do the RELEASE. If you left out the second DROP, then issued > a RELEASE, the database would be corrupt, so SQLite issues the error message > to warn you about it. The version without the SAVEPOINT never has to worry > about you doing that. > > Simon.
http://www.sqlite.org/pragma.html#pragma_foreign_keys: "...foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT. " Regards, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
