SQLite defer_foreign_keys=ON still cascades deletes during table rebuilds (drop/rename), causing data loss

[anpete]

Bug description

Hey EF Core team! I think #35873 causes a bad data loss bug.

Summary

When PRAGMA defer_foreign_keys=ON is used to keep migrations transactional, table rebuilds that drop a parent table can still trigger ON DELETE CASCADE on child rows. This happens even if data is copied into temp tables first, because the temp child still references the old parent. Result: data is deleted during the rebuild.

Expected behavior

When using defer_foreign_keys=ON, a transactional migration that rebuilds tables should not lose child rows purely because the parent table is dropped and recreated.

Actual behavior

Child rows are deleted during the table rebuild even with defer_foreign_keys=ON, because SQLite enforces FK actions during DDL. This causes ON DELETE CASCADE to fire when the parent table is dropped.

Minimal SQL repro (SQLite only)

This shows the core behavior without EF Core:

PRAGMA foreign_keys = ON;
PRAGMA defer_foreign_keys = 1;

DROP TABLE IF EXISTS Child;
DROP TABLE IF EXISTS Parent;

CREATE TABLE Parent (
    Id INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE Child (
    Id INTEGER NOT NULL PRIMARY KEY,
    FOREIGN KEY (Id) REFERENCES Parent (Id) ON DELETE CASCADE
);

INSERT INTO Parent (Id) VALUES (1);
INSERT INTO Child (Id) VALUES (1);

BEGIN TRANSACTION;
DROP TABLE Parent;
COMMIT;

SELECT COUNT(*) AS ChildCount_WithDeferForeignKeys FROM Child;
-- Actual: 0

EF-style rebuild repro (temp tables)

This simulates the EF Core migration pattern with temp tables:

PRAGMA foreign_keys = ON;
PRAGMA defer_foreign_keys = 1;

DROP TABLE IF EXISTS Child;
DROP TABLE IF EXISTS Parent;

CREATE TABLE Parent (
    Id INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE Child (
    Id INTEGER NOT NULL PRIMARY KEY,
    FOREIGN KEY (Id) REFERENCES Parent (Id) ON DELETE CASCADE
);

INSERT INTO Parent (Id) VALUES (1);
INSERT INTO Child (Id) VALUES (1);

BEGIN TRANSACTION;
CREATE TABLE ef_temp_Parent (
    Id INTEGER NOT NULL PRIMARY KEY
);
INSERT INTO ef_temp_Parent (Id)
SELECT Id FROM Parent;

CREATE TABLE ef_temp_Child (
    Id INTEGER NOT NULL PRIMARY KEY,
    FOREIGN KEY (Id) REFERENCES Parent (Id) ON DELETE CASCADE
);
INSERT INTO ef_temp_Child (Id)
SELECT Id FROM Child;

DROP TABLE Parent;
ALTER TABLE ef_temp_Parent RENAME TO Parent;
DROP TABLE Child;
ALTER TABLE ef_temp_Child RENAME TO Child;
COMMIT;

SELECT COUNT(*) AS ChildCount_WithDeferForeignKeys FROM Child;
-- Actual: 0

Notes

• defer_foreign_keys=ON does not disable FK actions during DDL.
• The cascade fires against both the original child and the temp child (which still references the old parent).
• Turning foreign_keys=OFF before the outer transaction avoids the issue, but it disables FK enforcement for the whole migration, which EF Core is likely trying to avoid.

Your code

Repro SQL in description.

Stack traces

Verbose output

EF Core version

main

Database provider

No response

Target framework

No response

Operating system

No response

IDE

No response

[anpete]

cc @ferferga

[cincuranet]

After doing some research, there does not seem to be a good solution out of this situation. So we decided to revert #35873. This will bring back the warning, but the warning is technically correct and developer knows to handle the situation accordingly (e.g. create database backup before migrating).

Fair to say the PRAGMA is used only when operation like table rebuild is required and for "normal" operations (like adding a column), the warning is not emitted.