SQLite migrations break when columns of tables that are referenced by foreign keys are changed

[aeneasr]

Description

Some SQLite migrations, such as drop_column have to use the temp table pattern where a table is renamed to _<original_table_name>_tmp, the update table is created, and then all data is copied from the temp table to the new table.

Since SQLite 3.28 (pop uses 3.32) however, renaming a table will also rename all the foreign key indices, leading to stale foreign key references such as:

FOREIGN KEY (<column_name>) REFERENCES _<original_table_name>_tmp (id) ON UPDATE NO ACTION ON DELETE CASCADE

A workaround for this is to disable foreign key checks and enable a pragma called legacy_alter_table:

PRAGMA legacy_alter_table=on;
PRAGMA foreign_keys=off;

The problem however is that PRAGMA can not be set during a transaction, only before or after, this however is problematic because all migrations run in a transaction. Therefore, it is not possible to add these statements to the fizz translator responsible for creating the temporary table.

Steps to Reproduce the Problem

See gobuffalo/fizz#96

Expected Behavior

It should be possible to change the PRAGMA behavior in the fizz translator.

Actual Behavior

It is not possible to use drop_column on a table that has foreign keys