<p><a href="https://www.postgresql.org/docs/9.1/static/ddl-constraints.html#DDL-CONSTRAINTS-FK" rel="nofollow">DOC </a>:</p>
<blockquote>
<p>Finally, we should mention that a foreign key must reference columns that either are a primary key or form a unique constraint.</p>
</blockquote>
<div class="highlight highlight-source-diff"><pre><span class="pl-md">--- a/lib/SQL/Translator/Producer/PostgreSQL.pm</span>
<span class="pl-mi1">+++ b/lib/SQL/Translator/Producer/PostgreSQL.pm</span>
<span class="pl-mdr">@@ -569,10 +569,21 @@</span> sub create_index
         = $index-&gt;name
         || join('_', $table_name, 'idx', ++$index_name{ $table_name });
 
<span class="pl-md"><span class="pl-md">-</span>    my $type = $index-&gt;type || NORMAL;</span>
     my @fields     =  $index-&gt;fields;
     return unless @fields;
 
<span class="pl-mi1"><span class="pl-mi1">+</span>    my $idx =  join '', @fields;</span>
<span class="pl-mi1"><span class="pl-mi1">+</span>    my $constraints =  $index-&gt;table-&gt;get_constraints;</span>
<span class="pl-mi1"><span class="pl-mi1">+</span>    for my $c ( @$constraints ) {</span>
<span class="pl-mi1"><span class="pl-mi1">+</span>        my $udx =  join '', map{ ref $_? $_-&gt;name : $_ } $c-&gt;field_names;</span>
<span class="pl-mi1"><span class="pl-mi1">+</span>        if( $idx eq $udx ) {</span>
<span class="pl-mi1"><span class="pl-mi1">+</span>            $index-&gt;type( 'UNIQUE' );</span>
<span class="pl-mi1"><span class="pl-mi1">+</span>            last;</span>
<span class="pl-mi1"><span class="pl-mi1">+</span>        }</span>
<span class="pl-mi1"><span class="pl-mi1">+</span>    }</span>
<span class="pl-mi1"><span class="pl-mi1">+</span>    my $type = $index-&gt;type || NORMAL;</span>
<span class="pl-mi1"><span class="pl-mi1">+</span></span>
<span class="pl-mi1"><span class="pl-mi1">+</span></span>
     my $index_using;
     my $index_where;
     for my $opt ( $index-&gt;options ) {</pre></div>
<p>This patch forces index to be <code>UNIQUE</code> if it is part of foreign constraint. The deployment script became:</p>
<div class="highlight highlight-source-diff"><pre>   "provider" smallint NOT NULL,
   PRIMARY KEY ("id")
 );
<span class="pl-md"><span class="pl-md">-</span>CREATE INDEX "contractor_idx_contractor_type_id" on "contractor" ("contractor_type_id");</span>
<span class="pl-mi1"><span class="pl-mi1">+</span>CREATE UNIQUE INDEX "contractor_idx_contractor_type_id" on "contractor" ("contractor_type_id");</span>
 
 ;
 --</pre></div>
<p>Without this patch we get error:</p>
<pre><code>ERROR:  there is no unique constraint matching given keys for referenced table "contractor_type"
</code></pre>
<pre><code>$(which dbic-migration) --schema_class HyperMouse::Schema --database PostgreSQL -Ilib install
Since this database is not versioned, we will assume version 2
Reading configurations from /home/kes/work/projects/tucha/monkeyman/share/fixtures/2/conf
failed to run SQL in /home/kes/work/projects/tucha/monkeyman/share/migrations/PostgreSQL/deploy/2/001-auto.sql: DBIx::Class::DeploymentHandler::DeployMethod::SQL::Translator::try {...} (): DBI Exception: DBD::Pg::db do failed: ERROR:  there is no unique constraint matching given keys for referenced table "contractor_type" at inline delegation in DBIx::Class::DeploymentHandler for deploy_method-&gt;deploy (attribute declared in /home/kes/work/projects/tucha/monkeyman/local/lib/perl5/DBIx/Class/DeploymentHandler/WithApplicatorDumple.pm at line 51) line 18
 (running line 'ALTER TABLE "contractor" ADD CONSTRAINT "contractor_fk_contractor_type_id" FOREIGN KEY ("contractor_type_id") REFERENCES "contractor_type" ("contractor_type_id") ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE') at /home/kes/work/projects/tucha/monkeyman/local/lib/perl5/DBIx/Class/DeploymentHandler/DeployMethod/SQL/Translator.pm line 248.
DBIx::Class::Storage::TxnScopeGuard::DESTROY(): A DBIx::Class::Storage::TxnScopeGuard went out of scope without explicit commit or error. Rolling back. at /home/kes/work/projects/tucha/monkeyman/local/bin/dbic-migration line 0
DBIx::Class::Storage::TxnScopeGuard::DESTROY(): A DBIx::Class::Storage::TxnScopeGuard went out of scope without explicit commit or error. Rolling back. at /home/kes/work/projects/tucha/monkeyman/local/bin/dbic-migration line 0
Makefile:123: recipe for target 'dbdeploy' failed
make: *** [dbdeploy] Error 255
</code></pre>
<p>This maybe applied after <a class="issue-link js-issue-link" data-error-text="Failed to load issue title" data-id="158200708" data-permission-text="Issue title is private" data-url="https://github.com/dbsrgits/sql-translator/issues/82" href="https://github.com/dbsrgits/sql-translator/issues/82">#82</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/104">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/AASeAqi9lDjIiFYb2c7ROSGGoEOqI1A9ks5uD1-cgaJpZM4VFbB9">mute the thread</a>.<img src="https://github.com/notifications/beacon/AASeAk5BhIgu9AuW7eCpIj0SDkE-G9ASks5uD1-cgaJpZM4VFbB9.gif" height="1" width="1" alt="" /></p>
<script type="application/json" data-scope="inboxmarkup">{"api_version":"1.0","publisher":{"api_key":"05dde50f1d1a384dd78767c55493e4bb","name":"GitHub"},"entity":{"external_key":"github/dbsrgits/sql-translator","title":"dbsrgits/sql-translator","subtitle":"GitHub repository","main_image_url":"https://assets-cdn.github.com/images/email/message_cards/header.png","avatar_image_url":"https://assets-cdn.github.com/images/email/message_cards/avatar.png","action":{"name":"Open in GitHub","url":"https://github.com/dbsrgits/sql-translator"}},"updates":{"snippets":[{"icon":"DESCRIPTION","message":"PostgreSQL requires unique index for foreign keys (#104)"}],"action":{"name":"View Issue","url":"https://github.com/dbsrgits/sql-translator/issues/104"}}}</script>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/dbsrgits/sql-translator/issues/104",
"url": "https://github.com/dbsrgits/sql-translator/issues/104",
"name": "View Issue"
},
"description": "View this Issue on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
},
{
"@type": "MessageCard",
"@context": "http://schema.org/extensions",
"hideOriginalBody": "false",
"originator": "AF6C5A86-E920-430C-9C59-A73278B5EFEB",
"title": "PostgreSQL requires unique index for foreign keys (#104)",
"sections": [
{
"text": "",
"activityTitle": "**Eugen Konkov**",
"activityImage": "https://assets-cdn.github.com/images/email/message_cards/avatar.png",
"activitySubtitle": "@KES777",
"facts": [
{
"name": "Repository: ",
"value": "dbsrgits/sql-translator"
},
{
"name": "Issue #: ",
"value": 104
}
]
}
],
"potentialAction": [
{
"name": "Add a comment",
"@type": "ActionCard",
"inputs": [
{
"isMultiLine": true,
"@type": "TextInput",
"id": "IssueComment",
"isRequired": false
}
],
"actions": [
{
"name": "Comment",
"@type": "HttpPOST",
"target": "https://api.github.com",
"body": "{\n\"commandName\": \"IssueComment\",\n\"repositoryFullName\": \"dbsrgits/sql-translator\",\n\"issueId\": 104,\n\"IssueComment\": \"{{IssueComment.value}}\"\n}"
}
]
},
{
"name": "Close issue",
"@type": "HttpPOST",
"target": "https://api.github.com",
"body": "{\n\"commandName\": \"IssueClose\",\n\"repositoryFullName\": \"dbsrgits/sql-translator\",\n\"issueId\": 104\n}"
},
{
"targets": [
{
"os": "default",
"uri": "https://github.com/dbsrgits/sql-translator/issues/104"
}
],
"@type": "OpenUri",
"name": "View on GitHub"
},
{
"name": "Unsubscribe",
"@type": "HttpPOST",
"target": "https://api.github.com",
"body": "{\n\"commandName\": \"MuteNotification\",\n\"threadId\": 353742973\n}"
}
],
"themeColor": "26292E"
}
]</script>