Re: [sqlite] Foreign Key constraint problem while dropping tables inside transaction
Понедельник, 13 мая 2013, 17:03 +01:00 от Simon Davies : >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. " Ups. My fault, must have read docs more carefully. Thank you. > >Regards, >Simon >___ >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
Re: [sqlite] Foreign Key constraint problem while dropping tables inside transaction
On 13 May 2013 16:52, Simon Slavin wrote: > > On 13 May 2013, at 3:54pm, Григорий Григоренко 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
Re: [sqlite] Foreign Key constraint problem while dropping tables inside transaction
On 13 May 2013, at 3:54pm, Григорий Григоренко 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users