Foreign Key already exists For Relation Error in EF6

Question

I am using Entity Framework 6.4.x with Npgsql and code first approach.

I created the following entity:

    public class UserBankAccountTransaction
    {
        public long PayerBankAccountId { get; set; }
        
        [ForeignKey(nameof(PayerBankAccountId))]
        public virtual UserBankAccount PayerBankAccount { get; set; }
        
        public long PayeeBankAccountId { get; set; }
        
        [ForeignKey(nameof(PayeeBankAccountId))]
        public virtual UserBankAccount PayeeBankAccount { get; set; }
        
        [Required]
        public int Amount { get; set; }
    }

That is all, now when I do Add-Migration it creates a following migration:

    CreateTable(
                "public.UserBankAccountTransactions",
                c => new
                    {
                        Id = c.Long(nullable: false, identity: true),
                        PayerBankAccountId = c.Long(nullable: false),
                        PayeeBankAccountId = c.Long(nullable: false),
                        Amount = c.Int(nullable: false),
                        CreatedDate = c.DateTime(nullable: false),
                        ModifiedDate = c.DateTime(nullable: false),
                    })
                .PrimaryKey(t => t.Id)
                .ForeignKey("public.UserBankAccounts", t => t.PayeeBankAccountId, cascadeDelete: true)
                .ForeignKey("public.UserBankAccounts", t => t.PayerBankAccountId, cascadeDelete: true)
                .Index(t => t.PayerBankAccountId)
                .Index(t => t.PayeeBankAccountId);

As you can see above in the automatically generated migration that "public.UserBankAccounts" is a foreign key being generated two times with same name.

Due to this When I Update-Migration I get this error:

constraint "FK_public.UserBankAccountTransactions_public.UserBankAccounts_P" for relation "UserBankAccountTransactions" already existsa

Problem

Isn't there a way to add more than one References/ForeignKeys to same table in EF6?

Answer 1

Postgres has limit for maximum length of identifier - 63 bytes, so both of your foreign keys end up with the same name - FK_public.UserBankAccountTransactions_public.UserBankAccounts_P.

So you need to find a way to specify FK name. Not sure that it can be done via attribute/fluent api (at least I was not able to find it) but as workaround you should be able to specify it via name parameter of TableBuilder<TColumns>.ForeignKey method in the generated migration (which is usually not a good thing to do):

.ForeignKey("public.UserBankAccounts", t => t.PayeeBankAccountId, cascadeDelete: true, name: "FK_UBAT_UBA_PayeeBankAccountId" )
.ForeignKey("public.UserBankAccounts", t => t.PayerBankAccountId, cascadeDelete: true, name: "FK_UBAT_UBA_PayerBankAccountId")