[DBIx-Class-Devel] [dbsrgits/sql-translator] PostgreSQL requires unique index for foreign keys (#104)

[Eugen Konkov]

[DOC ](https://www.postgresql.org/docs/9.1/static/ddl-constraints.html#DDL-CONSTRAINTS-FK):

>Finally, we should mention that a foreign key must reference columns that either are a primary key or form a unique constraint.

```diff
--- a/lib/SQL/Translator/Producer/PostgreSQL.pm
+++ b/lib/SQL/Translator/Producer/PostgreSQL.pm
@@ -569,10 +569,21 @@ sub create_index
         = $index->name
         || join('_', $table_name, 'idx', ++$index_name{ $table_name });
 
-    my $type = $index->type || NORMAL;
     my @fields     =  $index->fields;
     return unless @fields;
 
+    my $idx =  join '', @fields;
+    my $constraints =  $index->table->get_constraints;
+    for my $c ( @$constraints ) {
+        my $udx =  join '', map{ ref $_? $_->name : $_ } $c->field_names;
+        if( $idx eq $udx ) {
+            $index->type( 'UNIQUE' );
+            last;
+        }
+    }
+    my $type = $index->type || NORMAL;
+
+
     my $index_using;
     my $index_where;
     for my $opt ( $index->options ) {
```

This patch forces index to be `UNIQUE` if it is part of foreign constraint. The deployment script became:

```diff
   "provider" smallint NOT NULL,
   PRIMARY KEY ("id")
 );
-CREATE INDEX "contractor_idx_contractor_type_id" on "contractor" ("contractor_type_id");
+CREATE UNIQUE INDEX "contractor_idx_contractor_type_id" on "contractor" ("contractor_type_id");
 
 ;
 --
```

Without this patch we get error:

    ERROR:  there is no unique constraint matching given keys for referenced table "contractor_type"

```
$(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->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
```

This maybe applied after #82 

-- 
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/104
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class-devel/attachments/20180706/85147736/attachment.htm>