>"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
