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)
);

Example: https://github.com/dboehmer/coocook/blob/6535a11e3525ba776d05e3e087331e2a2a15656e/share/ddl/SQLite/upgrade/21-22/001-auto.sql

This is invalid SQL.

I could not find that detail in the SQLite docs. The only page that explicitly discusses this that I found is http://sqlite.1065341.n5.nabble.com/Foreign-keys-amp-TEMPORARY-tables-td92306.html

You can see here that this is just ignored by default and throws an error if PRAGMA foreign_keys is on:

1. is ignored
2. implicitly references temp. schema
3. invalid syntax

$ sqlite3 -init /dev/null
-- Loading resources from /dev/null
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> CREATE TABLE first (id);
sqlite> CREATE TEMPORARY TABLE second (first_id REFERENCES first(id) );
sqlite> INSERT INTO second VALUES(42);
sqlite> 

$ sqlite3 -init /dev/null
-- Loading resources from /dev/null
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> PRAGMA foreign_keys = on;
sqlite> CREATE TABLE first (id);
sqlite> CREATE TEMPORARY TABLE second (first_id REFERENCES first(id) );
sqlite> INSERT INTO second VALUES(42);
Error: no such table: temp.first
sqlite> 

$ sqlite3 -init /dev/null
-- Loading resources from /dev/null
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> CREATE TABLE first (id);
sqlite> CREATE TEMPORARY TABLE second (first_id REFERENCES main.first(id) );
Error: near ".": syntax error

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.

[frioux]

Thank you for taking the time to create such a detailed bug report! The actual generation of DDL is handled by SQL::Translator, so I suggest creating creating an issue there ( https://github.com/dbsrgits/sql-translator/issues) and maybe linking back to this one. Hope this helps!

…

On Wed, Dec 30, 2020, 4:53 PM Daniel Böhmer ***@***.***> wrote: 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) ); Example: https://github.com/dboehmer/coocook/blob/6535a11e3525ba776d05e3e087331e2a2a15656e/share/ddl/SQLite/upgrade/21-22/001-auto.sql *This is invalid SQL.* I could not find that detail in the SQLite docs. The only page that explicitly discusses this that I found is http://sqlite.1065341.n5.nabble.com/Foreign-keys-amp-TEMPORARY-tables-td92306.html You can see here that this is just ignored by default and throws an error if PRAGMA foreign_keys is on: 1. is ignored 2. implicitly references temp. schema 3. invalid syntax $ sqlite3 -init /dev/null-- Loading resources from /dev/null SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> CREATE TABLE first (id); sqlite> CREATE TEMPORARY TABLE second (first_id REFERENCES first(id) ); sqlite> INSERT INTO second VALUES(42); sqlite> $ sqlite3 -init /dev/null-- Loading resources from /dev/null SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> PRAGMA foreign_keys = on; sqlite> CREATE TABLE first (id); sqlite> CREATE TEMPORARY TABLE second (first_id REFERENCES first(id) ); sqlite> INSERT INTO second VALUES(42); Error: no such table: temp.first sqlite> $ sqlite3 -init /dev/null-- Loading resources from /dev/null SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> CREATE TABLE first (id); sqlite> CREATE TEMPORARY TABLE second (first_id REFERENCES main.first(id) ); Error: near ".": syntax error 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. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#76>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAB7Y47BGY3YFIDI5LVVBTSXPDQFANCNFSM4VO4FR5A> .

[mohawk2]

Might be worth closing this issue since it can't be solved by this project?