Constraint already exists

[roji]

From @agonzalezm on March 9, 2015 9:5

I have long table names and i get this error when EF code first is creating two constraint relations for the same tables because EF FK is longer and i think npgsql shorten it:

ERROR: 42710: constraint "FK_public.VirtualMachineNetworks_public.VirtualMachineNetworkCo" for relation "VirtualMachineNetworks" already exists

Is there any workaround for this?

Copied from original issue: npgsql/npgsql#523

[roji]

From @Emill on March 9, 2015 9:22

Hmm, PostgreSQL has a maxlength of the datatype "name" (63 characters by default) which you've hit... Maybe there is a good way of shorten a name if it is too long.

[roji]

From @gordon-matt on March 17, 2016 4:26

+1

[Bouke]

I have e.g. a parent/child relation on an entity that's 15 characters long, so EF creates a table for the m-to-n relationship. This m-to-n table has the name repeated, so that's 30 characters. The generated foreign key name becomes FK_dbo.LEFTTABLE_dbo.RIGHTTABLE_???, so that's 77 characters in total for the name.

Resulting in this query:

ALTER TABLE "dbo"."OurDomainObjectOurDomainObject" ADD CONSTRAINT "FK_dbo.OurDomainObjectOurDomainObject_dbo.OurDomainObject_OurDomainObject_Id1" FOREIGN KEY ("OurDomainObject_Id1") REFERENCES "dbo"."OurDomainObject" ("Id")

Resulting in this error, due to cutting off the key name:

42710: constraint "FK_dbo.OurDomainObjectOurDomainObject_dbo.OurDomainObject_Custo" for relation "OurDomainObjectOurDomainObject" already exists

How do people typically work around this? I've already found this that creates a custom SqlServerMigrationSqlGenerator.

[stephenfuqua]

Two responses, inspired by Microsoft's documentation on Fluent API - Relationships

@Bouke You can manually inject different names for the columns in the linking table. Something very like the following worked for me.

public class PrimaryEntity {
    public int Id { get; set; }
    public ICollection<SecondaryEntity> Secondaries { get; set; }
}

public class SecondaryEntity {
    public int Id { get; set; }
    public ICollection<PrimaryEntity> Primaries { get; set; }
}

public class MyContext {

 ...

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
    modelBuilder.Entity<PrimaryEntity>()
        .HasMany(t => t.Secondaries)
        .WithMany(t => t.Primaries)
        .Map(m =>
        {
            m.ToTable("PrimaryEntity_SecondaryEntity", "dbo");
            m.MapLeftKey("PrimaryEntityId");
            m.MapRightKey("SecondaryEntityId");
        });
 }
}

And now for the original problem - I have not tried this, but perhaps something like the following, replacing "VirtualMachineNetworkCo" with the non-truncated version of hte entity/table name.

public class YourContext {

 ...

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
    modelBuilder.Entity<VirtualMachineNetwork>()
    .HasRequired(c => c.VirtualMachineNetworkCo)
    .WithMany(t => t.VirtualMachineNetworks)
    .Map(m => m.MapKey("FK_VirtualMachineNetwork_VirtualMachineNetworkCo"));
 }
}