search

dbsrgits / sql-translator

SQL::Translator (SQLFairy)
http://sqlfairy.sourceforge.net/
82 stars 91 forks source link

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

Open dboehmer opened 3 years ago

dboehmer commented 3 years ago

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 https://github.com/frioux/DBIx-Class-DeploymentHandler/issues/76