I'm afraid I still didn't make any progress on this. It looks to me that PRAGMA foreign_key_check; and the deferred foreign key mechanism sometimes contradict each other, consequently at least one of these features must have a bug, but I don't know for sure which one, so I can't risk using any of them in production until the situation is resolved.
Do you have any suggestion how I should go about it? Can I escalate this to someone? Thanks in advance, Máté szmate1618 <szmate1...@gmail.com> ezt írta (időpont: 2018. nov. 23., P, 17:21): > >"PRAGMA foreign_keys = ?" is a property of the connection only, not the > >database file. So what advantage would there be in including the PRAGMA > >statements in the body of a transaction? > > Not much, if I do everything right, I guess. But what if I break the > foreign key integrity? > If it's inside a transaction, I can rollback easily, given that I realize > I broke it. > But if I don't, because foreign key checks are turned off, and I commit > everything before noticing that > something's wrong, that's a lot harder to fix. > > I can either take extra care not to break anything, or just do a backup of > the database file before > 'risky' transactions, but it would be much easier if I could just use > deferred foreign keys that don't > let me commit if the database is in an inconsistent state. > > Máté > > Dan Kennedy <danielk1...@gmail.com> ezt írta (időpont: 2018. nov. 23., P, > 16:30): > >> On 11/23/2018 09:54 PM, szmate1618 wrote: >> > Dear list members, >> > >> > I have the following problem, with which I'd like to request your aid: >> > >> > Currently, at version 3.25.2, SQLite only has a limited support for >> alter >> > table. E.g. you cannot change the datatype (type affinity) of a column, >> or >> > drop a column. >> > >> > The usual workaround is to create a new table with the desired schema, >> fill >> > it with data from the original table, drop the original table, and >> rename >> > the new one. But what if the original table is a parent table in a >> foreign >> > key relationship? >> > >> > The official solution >> > <https://www.sqlite.org/lang_altertable.html#otheralter> is turning >> foreign >> > keys off, making the changes you want, then turning foreign keys on. But >> > I'm slightly annoyed this cannot be done in a transaction (because >> these PRAGMA >> > foreign_keys =s don't take effect inside of transactions, so they need >> to >> > be issued before and after). >> >> >> "PRAGMA foreign_keys = ?" is a property of the connection only, not the >> database file. So what advantage would there be in including the PRAGMA >> statements in the body of a transaction? >> >> Dan. >> >> >> >> > >> > I'd like to use deferred foreign keys instead. I have 3 queries, one of >> > them seems to work, the two others do not. My questions are the >> following: >> > >> > - Does the seemingly working query work by design? Or it's just a >> > fortunate(?) constellation of multiple factors, and depending on >> other >> > tables or new data in the database it might break in the future? >> Somewhat >> > like undefined behavior in C++? >> > - Why do the other ones not work? How are they different from the >> first >> > one? >> > >> > Setup >> > >> > PRAGMA foreign_keys = OFF; >> > DROP TABLE IF EXISTS Parent;CREATE TABLE Parent(A TEXT UNIQUE, >> > COLUMN_TO_DROP FLOAT);INSERT INTO Parent VALUES('whatever', 0.0); >> > DROP TABLE IF EXISTS Child;CREATE TABLE Child(C TEXT REFERENCES >> > Parent(A) DEFERRABLE INITIALLY DEFERRED);INSERT INTO Child >> > VALUES('whatever'); >> > >> > PRAGMA foreign_keys = ON; >> > >> > Query1 - seems to be working as intended >> > >> > BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE >> > Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT INTO Parent SELECT * >> > FROM Temp;DROP TABLE Temp;COMMIT; >> > >> > Query2 - create [...] as select [...] fails >> > >> > BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE >> > Parent;CREATE TABLE Parent AS SELECT * FROM Temp; -- different >> > from Query1CREATE UNIQUE INDEX ParentIndex on Parent(A); -- different >> > from Query1DROP TABLE Temp;COMMIT; >> > >> > Result: >> > >> > sqlite> PRAGMA foreign_key_check; >> > sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A) >> > DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent(A TEXT);CREATE >> > UNIQUE INDEX ParentIndex on Parent(A); >> > sqlite> SELECT * FROM Parent; >> > whatever >> > >> > Query3 - insert into [...] fails >> > >> > BEGIN TRANSACTION;CREATE TABLE Temp (A TEXT UNIQUE); -- different >> > from Query1INSERT INTO Temp SELECT A FROM Parent; -- different from >> > Query1DROP TABLE Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT >> > INTO Parent SELECT * FROM Temp;DROP TABLE Temp;COMMIT; >> > >> > Result: >> > >> > sqlite> PRAGMA foreign_key_check; >> > sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A) >> > DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent (A TEXT UNIQUE); >> > sqlite> SELECT * FROM Parent; >> > whatever >> > >> > Note that PRAGMA foreign_key_check does not report any problem in any of >> > the cases. >> > >> > >> > I posted an identical question on StackOverflow, but no one was able to >> > provide any information so far. Thanks in advance! >> > >> > Máté Szabó >> > _______________________________________________ >> > sqlite-users mailing list >> > sqlite-users@mailinglists.sqlite.org >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
