<p></p>
<p>I use App::DH and DBIC-DeploymentHandler to create migration files, currently for SQLite and PostgreSQL. After I've made my application enforce <code>PRAGMA foreign_keys = on</code> for SQLite I found that the generated SQL is invalid. For complex table changes a temporary table is created like this:</p>
<div class="highlight highlight-source-sql"><pre>CREATE TEMPORARY TABLE mytable_temp_alter (
  <span class="pl-c"><span class="pl-c">--</span> copy columns</span>
  <span class="pl-k">FOREIGN KEY</span> ( mycolumn_id ) <span class="pl-k">REFERENCES</span> othertable(id)
);</pre></div>
<p><strong>This is invalid SQL.</strong></p>
<p>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 <code>PRAGMA foreign_keys</code> is on they will be checked during insertion.</p>
<p>I filed this bug before at DBIC-DeploymentHandler. For more details and research see <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="776697143" data-permission-text="Title is private" data-url="https://github.com/frioux/DBIx-Class-DeploymentHandler/issues/76" data-hovercard-type="issue" data-hovercard-url="/frioux/DBIx-Class-DeploymentHandler/issues/76/hovercard" href="https://github.com/frioux/DBIx-Class-DeploymentHandler/issues/76">frioux/DBIx-Class-DeploymentHandler#76</a></p>

<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">&mdash;<br />You are receiving this because you are subscribed to this thread.<br />Reply to this email directly, <a href="https://github.com/dbsrgits/sql-translator/issues/130">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/AACJ4ARMCAMMLVJLFL3WJX3SXPRLJANCNFSM4VO6YZOQ">unsubscribe</a>.<img src="https://github.com/notifications/beacon/AACJ4AWWWZ366O7FDVMACZTSXPRLJA5CNFSM4VO6YZO2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4LSMBA6Q.gif" height="1" width="1" alt="" /></p>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/dbsrgits/sql-translator/issues/130",
"url": "https://github.com/dbsrgits/sql-translator/issues/130",
"name": "View Issue"
},
"description": "View this Issue on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
}
]</script>