HHHH
MHMHM
TTT
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 (I have not tested this) - although I expect it will still fail as you still have violated fkey constraints when the transaction goes to complete.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 = ON/PRAGMA defer_foreign_keys = OFF but it does not work either...TMTPRAGMA defer_foreign_keys, Table B was modified and my table A are cascaded without any errors like before
JJT
.wrangler/state/v3/d1. It's a sqlite file, you could explore or even modify it using your favorite toolTJTJJTTTJJ
TJT
ITIIITI
PRAGMA foreign_keys = OFF;PRAGMA foreign_keys = ONDROP TABLEb_idON DELETE CASCADE ON UPDATE CASCADEPRAGMA defer_foreign_keys = onPRAGMA defer_foreign_keys = ONPRAGMA defer_foreign_keys = OFFPRAGMA defer_foreign_keys.wrangler/state/v3/d1The Database Tools and SQL plugin is available only in IntelliJ IDEA Ultimate.✘ [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;
create table b_temp ...;
drop table b...;
alter table b_tmp rename to b;
PRAGMA foreign_keys = ON;
CDCloudflare Developers / workers-and-pages-helpCDCloudflare Developers / pages-helpCDCloudflare Developers / general-helpCDCloudflare Developers / workers-and-pages-help