[DBIx-Class-Devel] [dbsrgits/sql-translator] Generated SQL for SQLite creates invalid FKs from temporary tables referencing main tables (#130)

[Daniel Böhmer]

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:

```sql
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

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/dbsrgits/sql-translator/issues/130
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class-devel/attachments/20201230/0170d81f/attachment.htm>