Generated SQL for SQLite creates invalid FKs from temporary tables referencing main tables

[dboehmer]

I use App::DH and DBIC-DeploymentHandler to create migration files, currently for SQLite and PostgreSQL. After I've made my application enforce PRAGMA foreign_keys = on for SQLite I found that the generated SQL is invalid. For complex table changes a temporary table is created like this:

CREATE TEMPORARY TABLE mytable_temp_alter (
  -- copy columns
  FOREIGN KEY ( mycolumn_id ) REFERENCES othertable(id)
);

This is invalid SQL.

For SQLite I see no other solution that to just skip the FKs for the temporary table. The new main table will have FKs again and if PRAGMA foreign_keys is on they will be checked during insertion.

I filed this bug before at DBIC-DeploymentHandler. For more details and research see frioux/DBIx-Class-DeploymentHandler#76