T
PRAGMA foreign_keys = OFF; at the beginning and PRAGMA foreign_keys = ON at the end of the migration but it still drops all of my records in table A which reference table B are deleted.
MDROP TABLE commands: https://www.sqlite.org/foreignkeys.html#fk_schemacommandsT
I forgot to mention my scenario:
b_id which reference table B column idON DELETE CASCADE ON UPDATE CASCADEMIf foreign key constraints are enabled when it is prepared, the DROP TABLE command performs an implicit DELETE to remove all rows from the table before dropping it. The implicit DELETE does not cause any SQL triggers to fire, but may invoke foreign key actions or constraint violations. If an immediate foreign key constraint is violated, the DROP TABLE statement fails and the table is not dropped. If a deferred foreign key constraint is violated, then an error is reported when the user attempts to commit the transaction if the foreign key constraint violations still exist at that point. Any "foreign key mismatch" errors encountered as part of an implicit DELETE are ignored.
PRAGMA defer_foreign_keys = on
MTT
TTM
follow up: it seems like bracketing it with PRAGMA defer_foreign_keys = ON; and PRAGMA defer_foreign_keys = OFF; does make it work. I am not familiar enough with arcane SQLite stuff to give a super informed view on whether this should be automatically applied to every migration, but I suspect it might be a good idea - my understanding is that the foreign key constraints will still be checked, but just at the time that the transaction is committed.
TPRAGMA defer_foreign_keys = ONPRAGMA defer_foreign_keys = OFFPRAGMA defer_foreign_keys
JJT
.wrangler/state/v3/d1
TJT
ITIIITIM
wrangler d1 export
I
II
JJJ✘ [ERROR] Wrangler could not process the provided SQL file, as it contains several transactions.
D1 runs your SQL in a transaction for you.
Please export an SQL file from your SQLite database and try again.PRAGMA foreign_keys = OFF;PRAGMA foreign_keys = ONDROP TABLEb_idON DELETE CASCADE ON UPDATE CASCADEPRAGMA foreign_keys = OFF;
create table b_temp ...;
drop table b...;
alter table b_tmp rename to b;
PRAGMA foreign_keys = ON;