Temporarily Turn off Cascade On Delete

Question

I'm trying to write a new migration for updating our sqlite database using the database provider. The process for altering columns is described in their documentation ALTER TABLE.

I need to make a change to a parent table having children with foreign key constraints set to cascade on delete. I've implemented the steps from documentation but when I drop the old table it deletes all the records in the child tables. The steps in the documentation:

1. Create new table
2. Copy data
3. Drop old table
4. Rename new into old

/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(
        "PRAGMA foreign_keys=off;\r\n" +
        "PRAGMA ignore_check_constraints = on;" +
        "PRAGMA legacy_alter_table=on;\r\n" +

        // Step 1.
        "CREATE TABLE \"PROCEDURES_TEMP\" (\r\n" +
        "    \"PROCEDURE_ID\" TEXT NOT NULL CONSTRAINT \"PK_PROCEDURES\" PRIMARY KEY,\r\n" +
        "    \"DESCRIPTION\" TEXT NOT NULL,\r\n" +
        "    \"IS_PRODUCTION\" INTEGER NOT NULL DEFAULT (0)\r\n" +
        ");\r\n" +

        // Step 2.
        "INSERT INTO \"PROCEDURES_TEMP\"(\"PROCEDURE_ID\", \"DESCRIPTION\", \"IS_PRODUCTION\")\r\n" +
                            "SELECT \"PROCEDURE_ID\", \"DESCRIPTION\", \"IS_PRODUCTION\" \r\n" +
                            "FROM \"PROCEDURES\";\r\n" +
            
        // Step 3.
        "DROP TABLE \"PROCEDURES\";\r\n" +
        // Step 4.
        "ALTER TABLE \"PROCEDURES_TEMP\" RENAME TO \"PROCEDURES\";\r\n" +

        "PRAGMA foreign_keys=on;\r\n" +
        "PRAGMA ignore_check_constraints = off;" +
        "PRAGMA legacy_alter_table=off;\r\n"
        );
}

The child table looks like

CREATE TABLE \"FLOWS\" (
    \"FLOW_ID\" INTEGER NOT NULL,
    \"PROCEDURE_ID\" TEXT NOT NULL,
    \"DESCRIPTION\" TEXT NULL,
    \"SEQUENCE_NUMBER\" INTEGER NOT NULL DEFAULT (1),
    CONSTRAINT \"PK_FLOWS\" PRIMARY KEY (\"FLOW_ID\", \"PROCEDURE_ID\"),
    CONSTRAINT \"FK_FLOWS_PROCEDURES\" FOREIGN KEY (\"PROCEDURE_ID\") REFERENCES \"PROCEDURES\"  (\"PROCEDURE_ID\") ON DELETE CASCADE\r\n);

I'm not 100% that the cascade on delete is what is causing the issue. If I don't drop the old table all of the data is still there. I've tried a number of different versions, like making it 2 operations instead of one. Will I need to temporarily store all the child data and reinsert it? Is there something I'm missing?

Answer 1

I had this issue in a migration that changed two tables. The issue was that one table was the parent table of the other.

The autogenerated migration code handled both tables independently one after another. The solution in my case was to reorder the code to copy both tables’ rows to temporary tables at first and then drop & re-create both tables and copy back the data.

The autogenerated code looked like this (simplified):

-- child_table
CREATE TEMPORARY TABLE child_table_tmp ...;
INSERT INTO child_table_tmp SELECT * FROM child_table;
DROP TABLE child_table;
CREATE TABLE child_table ...;
INSERT INTO child_table SELECT * FROM child_table_tmp;
DROP TABLE child_table_tmp;

-- parent_table
CREATE TEMPORARY TABLE parent_table_tmp ...;
INSERT INTO parent_table_tmp SELECT * FROM parent_table;
DROP TABLE parent_table;
CREATE TABLE parent_table ...;
INSERT INTO parent_table SELECT * FROM parent_table_tmp;
DROP TABLE parent_table_tmp;

I re-ordered statements to do it like this:

-- save child_table
CREATE TEMPORARY TABLE child_table_tmp ...;
INSERT INTO child_table_tmp SELECT * FROM child_table;

-- save parent_table
CREATE TEMPORARY TABLE parent_table_tmp ...;
INSERT INTO parent_table_tmp SELECT * FROM parent_table;

-- create new tables and re-insert data
DROP TABLE child_table;
CREATE TABLE child_table ...;
INSERT INTO child_table SELECT * FROM child_table_tmp;
DROP TABLE child_table_tmp;

DROP TABLE parent_table;
CREATE TABLE parent_table ...;
INSERT INTO parent_table SELECT * FROM parent_table_tmp;
DROP TABLE parent_table_tmp;

In case your child tables are not changed by the migration you might still be able to solve the issue by saving the child table rows beforehand and re-insert them.