Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
aeneasr opened this issue Jul 6, 2020 · 0 comments
Labels
bug Something isn't working

Comments

@aeneasr
Copy link
Member

aeneasr commented Jul 6, 2020

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

@aeneasr aeneasr changed the title SQLite Migrations break due to table renames SQLite migrations break when columns of tables that are referenced by foreign keys are changed Jul 6, 2020
@aeneasr aeneasr added the bug Something isn't working label Aug 27, 2020
aeneasr added a commit to gobuffalo/fizz that referenced this issue Aug 27, 2020
aeneasr added a commit to gobuffalo/fizz that referenced this issue Aug 27, 2020
aeneasr added a commit to gobuffalo/fizz that referenced this issue Aug 27, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

1 participant