🚀 Feature Request: Allow turning off foreign key check while migrating database.

[aperture147]

Some table altering tasks in SQLite cannot be achieved without replacing it with a new table (by dropping - recreating the table) (like adding foreign keys, changing primary keys, updating column type). Dropping a table which has column referenced by other tables with ON DELETE CASCADE will delete all records of the child tables.

Example: Assume that we have Table A and B. Table B has column a_id referencing (ON DELETE CASCADE) table A primary key id. For some reason I have to modify Table A by dropping and recreating table A (with original value), ALL records from table B are deleted by cascading. I'm trying to find a workaround for this situation as mentioned in this sqlx issue and some suggestion from Matt in Discord, but none of them work:

1. Try bracketing the migration with PRAGMA foreign_keys = OFF; and PRAGMA foreign_keys = ON;:

PRAGMA foreign_keys = OFF;
create table a_temp ...;
insert into table a_temp select from a ...;
drop table a;
alter table a_temp rename to a;
PRAGMA foreign_keys = ON;

2. Try bracketing the migration with PRAGMA defer_foreign_keys = OFF; and PRAGMA defer_foreign_keys = ON;:

PRAGMA defer_foreign_keys = ON;
create table a_temp ...;
insert into table a_temp select from a ...;
drop table a;
alter table a_temp rename to a;
PRAGMA defer_foreign_keys = OFF;

3. Try committing the current transaction and create a new one later:

COMMIT TRANSACTION;
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;

create table a_temp ...;
insert into table a_temp select from a ...;
drop table a;
alter table a_temp rename to a;

COMMIT TRANSACTION;
PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;

The workaround 1 and 2 completed but all records in table B is still deleted. The workaround 3 threw and error like this:

✘ [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.

Currently I have to copy data from table B to a temporally table then reinsert it to table B later.

create table b_temp;
insert into table b_temp select from b ...;

create table a_temp ...;
insert into table a_temp select from a ...;
drop table a;
alter table a_temp rename to a;

insert Into table b select from b_temp ...;
drop table b_temp;

This is a huge problem when I could have table B1, B2, B3 and more, all of them reference to table A, each table contains a few hundred thousands of records and more important, table B1, B2, B3 could be referenced from other tables too. This is really inefficient since it would definitely make a hit to billing metrics and might impact the traffic and make a migration takes longer to finish (which might affect the traffic to my D1).

Will there be a feature address this situation or is there any existing solution?

[aperture147]

Hi, is there anyone reading this issue?

[geelen]

Hi @aperture147, my apologies. I'm looking into this exact issue as part of https://github.com/cloudflare/workers-sdk/issues/5683. Will make a record to update this issue once I have a resolution.

[aperture147]

Hi @aperture147, my apologies. I'm looking into this exact issue as part of #5683. Will make a record to update this issue once I have a resolution.

Thanks. It would be nice to have disable foreign key check option on remote migration. For now I still have to drop and recreate every single tables and recreate it again to add foreign keys.

[Gerbuuun]

Just want to add to this that this is indeed very annoying. Any changes to a table that requires recreating it will cascade to other referenced tables. I cannot make the required changes to my database atm.. (at least not trivially)

[colecrouter]

I kept running into various errors trying something similar (foreign key constraint failed, LLVM syntax errors). I noticed @aperture147's second example is backwards.

PRAGMA defer_foreign_keys = ON;

CREATE TABLE ...

PRAGMA defer_foreign_keys = OFF;

Doing it exactly like this worked for me. Hope this helps.

Not sure exactly where my issue was. Possible causes:

• Not capitalizing ON/OFF
• Not turning off after
• Not putting it at the very start/end of file
• Missing semicolon

Docs are a bit messy as far as this goes; one page uses true/false, another uses on/off. Neither use semicolons.

[Gerbuuun]

Does not work for me. Rows are still deleted or set to null.

edit: this is my migration file btw

PRAGMA defer_foreign_keys = ON;
--> statement-breakpoint

DROP INDEX `user_email_idx`;
--> statement-breakpoint
DROP INDEX `user_public_idx`;
--> statement-breakpoint
CREATE TABLE `new_user` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`public_id` text NOT NULL,
	`first_name` text NOT NULL,
	`last_name` text NOT NULL,
	`email` text NOT NULL,
	`verified` integer NOT NULL,
	`created_at` text NOT NULL,
	`updated_at` text NOT NULL,
	`deleted_at` text
);
--> statement-breakpoint
INSERT INTO `new_user` (`id`, `public_id`, `first_name`, `last_name`, `email`, `verified`, `created_at`, `updated_at`, `deleted_at`)
SELECT `id`, `public_id`, `first_name`, `last_name`, `email`, `verified`, `created_at`, `updated_at`, `deleted_at`
FROM `user`;
--> statement-breakpoint
DROP TABLE `user`;
--> statement-breakpoint
ALTER TABLE `new_user` RENAME TO `user`;
--> statement-breakpoint
UPDATE sqlite_sequence SET seq = (SELECT MAX(ID) FROM `user`) WHERE name = 'user';
--> statement-breakpoint
CREATE UNIQUE INDEX `user_public_idx` ON `user` (`public_id`);
--> statement-breakpoint
CREATE UNIQUE INDEX `user_email_idx` ON `user` (`email`);
--> statement-breakpoint

PRAGMA defer_foreign_keys = OFF;
--> statement-breakpoint

edit 2: first renaming the old table and then creating the new table (which is highly discouraged by the sqlite docs) also does not work

[aperture147]

I kept running into various errors trying something similar (foreign key constraint failed, LLVM syntax errors). I noticed @aperture147's second example is backwards.

PRAGMA defer_foreign_keys = ON;

CREATE TABLE ...

PRAGMA defer_foreign_keys = OFF;

Doing it exactly like this worked for me. Hope this helps.

Not sure exactly where my issue was. Possible causes:

• Not capitalizing ON/OFF
• Not turning off after
• Not putting it at the very start/end of file
• Missing semicolon

Docs are a bit messy as far as this goes; one page uses true/false, another uses on/off. Neither use semicolons.

Hi, sorry for the wrong example. I've corrected the original example. Actually I've tried defer_foreign_keys = ON then defer_foreign_keys = OFF first but it does not work, so I later tried OFF then ON (although it does not make any sense) and it expectedly does not work either. That's why the example has the defer_foreign_key backward.

It seems like defer_foreign_keys only works for inserting new records, I've tried defer_foreign_keys for inserting new records and it works. But it does not work on deleting old records or dropping the table as shown.

[hrueger]

To be honest, I'd probably rather mark that as a bug - I almost lost data today due to a migration silently setting those values to NULL 😬 If you need an easily runnable reproduction, checkout https://github.com/hrueger/prisma-24540 (not prisma specific). If there's anything I can test or help, just let me know 👍

[mhuretski]

Hi, are there any updates on this?

As far as I understand current workaround is to copy all related tables affected by ON DELETE CASCADE, drop original tables and then rename new ones back to original names?

[aperture147]

Hi, are there any updates on this?

As far as I understand current workaround is to copy all related tables affected by ON DELETE CASCADE, drop original tables and then rename new ones back to original names?

+1. Is there any update on this?

[IDovgalyuk]

Hi, are there any updates on this?

As far as I understand current workaround is to copy all related tables affected by ON DELETE CASCADE, drop original tables and then rename new ones back to original names?

A workaround that works for me:

PRAGMA defer_foreign_keys = ON;
--> statement-breakpoint

CREATE TABLE `new_user` (
	`id` integer PRIMARY KEY NOT NULL,
	`first_name` text,
	`last_name` text NOT NULL
);
--> statement-breakpoint

INSERT INTO `new_user` (`id`, `first_name`, `last_name`)
SELECT `id`, `first_name`, `last_name`
FROM `user`;
--> statement-breakpoint

-- Making ids of related tables invalid, so they are not effected by ON DELETE CASCADE / SET NULL
UPDATE `order` SET `user_id` = -`user_id`
--> statement-breakpoint

DROP TABLE `user`;
--> statement-breakpoint

ALTER TABLE `new_user` RENAME TO `user`;
--> statement-breakpoint

-- And making ids correct again
UPDATE `order` SET `user_id` = -`user_id`
--> statement-breakpoint

PRAGMA defer_foreign_keys = OFF;
--> statement-breakpoint

[lykhonis]

Hi, are there any updates on this? As far as I understand current workaround is to copy all related tables affected by ON DELETE CASCADE, drop original tables and then rename new ones back to original names?

A workaround that works for me:

PRAGMA defer_foreign_keys = ON;
--> statement-breakpoint

CREATE TABLE `new_user` (
	`id` integer PRIMARY KEY NOT NULL,
	`first_name` text,
	`last_name` text NOT NULL
);
--> statement-breakpoint

INSERT INTO `new_user` (`id`, `first_name`, `last_name`)
SELECT `id`, `first_name`, `last_name`
FROM `user`;
--> statement-breakpoint

-- Making ids of related tables invalid, so they are not effected by ON DELETE CASCADE / SET NULL
UPDATE `order` SET `user_id` = -`user_id`
--> statement-breakpoint

DROP TABLE `user`;
--> statement-breakpoint

ALTER TABLE `new_user` RENAME TO `user`;
--> statement-breakpoint

-- And making ids correct again
UPDATE `order` SET `user_id` = -`user_id`
--> statement-breakpoint

PRAGMA defer_foreign_keys = OFF;
--> statement-breakpoint

Unfortunately this still nukes any other data that is bound by on delete cascade. Be very careful with this. What it seems to me currently there is only one way to migrate tables/columns by exporting all data, droping all tables, recreating, and inserting data back in.

[IDovgalyuk]

Unfortunately this still nukes any other data that is bound by on delete cascade. Be very careful with this. What it seems to me currently there is only one way to migrate tables/columns by exporting all data, droping all tables, recreating, and inserting data back in.

Found even better approach. Please consider this sample where we need recreate target table in transaction.

-- ARRANGE
CREATE TABLE `target` (
  `id` integer PRIMARY KEY NOT NULL,
  `data` text NOT NULL
);
INSERT INTO `target` (`id`, `data`)
VALUES (1, 'target_val_1'), (2, 'target_val_2');

CREATE TABLE `on_delete_restrict` (
  `id` integer PRIMARY KEY NOT NULL,
  `target_id` integer NULL,
  `data` text NOT NULL,
  FOREIGN KEY (`target_id`) REFERENCES `target` (`id`) ON DELETE RESTRICT
);
INSERT INTO `on_delete_restrict` (`id`, `target_id`, `data`)
VALUES (1, 1, 'on_delete_restrict_val_1'), (2, 2, 'on_delete_restrict_val_2');

CREATE TABLE `on_delete_cascade` (
  `id` integer PRIMARY KEY NOT NULL,
  `target_id` integer NULL,
  `data` text NOT NULL,
  FOREIGN KEY (`target_id`) REFERENCES `target` (`id`) ON DELETE CASCADE
);
INSERT INTO `on_delete_cascade` (`id`, `target_id`, `data`)
VALUES (1, 1, 'on_delete_cascade_val_1'), (2, 2, 'on_delete_cascade_val_2');

CREATE TABLE `on_delete_set_null` (
  `id` integer PRIMARY KEY NOT NULL,
  `target_id` integer NULL,
  `data` text NOT NULL,
  FOREIGN KEY (`target_id`) REFERENCES `target` (`id`) ON DELETE SET NULL
);

INSERT INTO `on_delete_set_null` (`id`, `target_id`, `data`)
VALUES (1, 1, 'on_delete_set_null_val_1'), (2, 2, 'on_delete_set_null_val_2');
  
-- ACT
BEGIN;

PRAGMA defer_foreign_keys = ON;

-- IMPORTANT to make PK invalid before dropping table
UPDATE `target` SET `id` = - `id`;

CREATE TABLE `__new_target` (
  `id` integer PRIMARY KEY NOT NULL,
  `data` text NOT NULL
);

INSERT INTO `__new_target` (`id`, `data`)
SELECT `id`, `data` || '_updated'
FROM `target`;

DROP TABLE `target`;

ALTER TABLE `__new_target` RENAME TO `target`;

-- IMPORTANT to make PK valid again
UPDATE `target` SET `id` = - `id`;
  
PRAGMA defer_foreign_keys = OFF;

COMMIT;

-- ASSERT
/*
	id	data
	1	target_val_1_updated
	2	target_val_2_updated
*/
SELECT * FROM target;

/*
	id	target_id	data
	1	1		on_delete_restrict_val_1
	2	2		on_delete_restrict_val_2
*/
SELECT * FROM `on_delete_restrict`;

/*
	id	target_id	data
	1	1		on_delete_cascade_val_1
	2	2		on_delete_cascade_val_2
*/
SELECT * FROM `on_delete_cascade`;

/*
	id	target_id	data
	1	1		on_delete_set_null_val_1
	2	2		on_delete_set_null_val_2
*/
SELECT * FROM `on_delete_set_null`;

/* empty table */
SELECT * FROM `pragma_foreign_key_check`;

This works on local database.

[takakikasuga]

• https://github.com/cloudflare/workers-sdk/issues/5438#issuecomment-2485001847

I found this problem too!!

but this problam is written in document!!

https://developers.cloudflare.com/d1/sql-api/sql-statements/#pragma-defer_foreign_keys--onoff

Note that setting PRAGMA defer_foreign_keys = ON does not prevent ON DELETE CASCADE actions from being executed. While foreign key constraint checks are deferred until the end of a transaction, ON DELETE CASCADE operations will remain active, consistent with SQLite's behavior.

[flashblaze]

Would be really helpful if there is any update on this. I'm using drizzle with this and after adding a column to my table which was being referenced by another table, the other table's data was completely nuked

[aperture147]

Would be really helpful if there is any update on this. I'm using drizzle with this and after adding a column to my table which was being referenced by another table, the other table's data was completely nuked

It seems like they forgot to fix this problem... might be a non-trivial one to fix. It's been a year since I create this issue but no update on this. Altering table is but very common in developing new features, it does not occur very often but everytime I alter tables, it's a super painful work to do and one mistake will nuke the whole database. This is really annoying since when the project grows up, the schema gets complicated which will make the chance of destroying my own database one step easier.

My current (terrible) workaround is abandon column that's suppose to be dropped and create a new child table with new columns which is supposed to be added to the parent table. This problem also discourages me to add features which require any "destructive" operation on the tables or store any real data on D1. I've migrated a lot of my data to Cloudflare KV and R2 and only kept data on D1 as minimal as possible, mainly metadata or the data header are kept and practically retire all the foreign keys. Cloudflare could have a dedicated migration state by enabling a flag in wrangler which will stall the database for a few seconds to migrate. This approach is not perfect but acceptable to me and I think it's also okay for a lot of people too.

I haven't try sqlite backend on Durable Object yet to verify that they have the same symptom like this or not, but even if it doesn't, using SQLite in DO is kinda overkill for me when I only need a hosted database service, not a "custom database engine" based on SQLite.

Let's hope this problem will be fixed in this year.

[kevinsimper]

Really surprised, I think this is not highlighted in the docs enough 😅

[Joelsz]

Is there any plan to address this problem? Currently there's no way to delete a table for recreation.

DROP TABLE fails:

PRAGMA foreign_keys = OFF;DROP TABLE table1;

✘ [ERROR] FOREIGN KEY constraint failed: SQLITE_CONSTRAINT

Transaction doesn't work either (I'm not using DO):

BEGIN;PRAGMA foreign_keys = OFF;DROP TABLE table1;COMMIT;

✘ [ERROR] To execute a transaction, please use the state.storage.transaction() or state.storage.transactionSync() APIs instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements. The JavaScript API is safer because it will automatically roll back on exceptions, and because it interacts correctly with Durable Objects' automatic atomic write coalescing.

[aperture147]

Is there any plan to address this problem? Currently there's no way to delete a table for recreation.

This problem comes from SQLite itself, not from D1. It's mentioned in document:

Calling PRAGMA defer_foreign_keys = off does not disable foreign key enforcement outside of the current transaction. If you have not resolved outstanding foreign key violations at the end of your transaction, it will fail with a FOREIGN KEY constraint failed error.

It seems like you've forgot to set the ON DELETE clause while creating the foreign key. If you don't set that, it will be default to ON DELETE RESTRICT, which does not allow you to delete any row in the parent table. Since the DROP TABLE command will delete all rows before actually deleting the table, it will restrict you from doing it.

By the way the problem I'm raising here might not related to the problem you are having, and what you want to do not be what you need. Even if the PRAGMA foreign_keys = OFF does bypass the foreign key checking, it will create a lot of orphaned rows which reference to nowhere, and setting ON DELETE CASCADE or ON DELETE SET NULL will nuke all the children table by deleting all the rows or set the column that reference the parent table to NULL.

[Joelsz]

I am using wrangler and was initially running them in seperate commands. Trying now in a single command it seems to have worked fine.

PRAGMA defer_foreign_keys = on;DROP TABLE table1;ALTER TABLE table1_new RENAME TO table1;PRAGMA defer_foreign_keys = off;

Thanks @aperture147 for pointing me in that direction.

Edit: It worked but deleted the reference value due to the ON DELETE SET NULL. I guess I will update all tables to ON DELETE NO ACTION

[zjx20]

I just experienced a data loss issue as well. After some investigation, I found the reproduction steps in SQLite:

1. Run a brand new SQLite database, for example, sqlite3 /tmp/test.db

2. Paste the following statements into the SQLite interactive console to create the parent and child tables and insert one record into each.

   -- Create the parent table
   CREATE TABLE parent (
       id INTEGER PRIMARY KEY
   );
   
   -- Create the child table with the ON DELETE CASCADE constraint
   CREATE TABLE child (
       id INTEGER PRIMARY KEY,
       parent_id INTEGER,
       CONSTRAINT "test1" FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE
   );
   
   -- Insert some data
   INSERT INTO parent (id) VALUES (1);
   INSERT INTO child (id, parent_id) VALUES (1, 1);
   
   -- Check the data
   SELECT * FROM parent;
   SELECT * FROM child;
   

3. Immediately after, run the following statements to simulate a migration of the parent table.

   -- Enforce foreign key constraint
   PRAGMA foreign_keys=ON;
   
   -- Simulate a migration for the parent table
   BEGIN;
   PRAGMA defer_foreign_keys=ON;
   PRAGMA foreign_keys=OFF;
   
   CREATE TABLE new_parent (
       id INTEGER PRIMARY KEY
   );
   INSERT INTO "new_parent" ("id") SELECT "id" FROM "parent";
   DROP TABLE parent;
   ALTER TABLE "new_parent" RENAME TO "parent";
   PRAGMA foreign_keys=ON;
   PRAGMA defer_foreign_keys=OFF;
   COMMIT;
   
   -- Check the data, the child table becomes empty!
   SELECT * FROM parent;
   SELECT * FROM child;
   

4. After execution, it was found that the data in the child table was deleted synchronously.

I feel that one of the problems is that the PRAGMA foreign_keys=OFF; statement within the transaction does not work. If we remove the PRAGMA foreign_keys=ON; statement before the transaction (which defaults to OFF), and re-execute the SQL, the data in the child table is preserved.

However, this is unfortunately not a solution, because when performing a migration, the entire SQL file is wrapped in a transaction, which means that PRAGMA foreign_keys=OFF; within the migration script is always ineffective.

As a workaround, I eventually changed all ON DELETE CASCADE to ON DELETE RESTRICT. While this makes the business logic a bit more troublesome, at least I won't lose data anymore.

[IDovgalyuk]

You can't change foreign_keys during the transaction https://sqlite.org/foreignkeys.html

It is not possible to enable or disable foreign key constraints in the middle of a [multi-statement transaction](https://sqlite.org/lang_transaction.html) (when SQLite is not in [autocommit mode](https://sqlite.org/c3ref/get_autocommit.html)). Attempting to do so does not return an error; it simply has no effect.

Try this workaround, it's works for me with ON DELETE CASCADE:

-- Enforce foreign key constraint
PRAGMA foreign_keys=ON;

-- Simulate a migration for the parent table
BEGIN;
PRAGMA defer_foreign_keys=ON;

CREATE TABLE new_parent (
    id INTEGER PRIMARY KEY
);
INSERT INTO "new_parent" ("id") SELECT "id" FROM "parent";

UPDATE parent set id = -id
DROP TABLE parent;

ALTER TABLE "new_parent" RENAME TO "parent";
PRAGMA defer_foreign_keys = OFF;
COMMIT;

-- Check the data, the child table becomes empty!
SELECT * FROM parent;
SELECT * FROM child;