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). 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