How to Insert data in table when Foreign Key is not a primary key

Question

I created a Model with Entity Framework Code First and have two classes

Product.cs

public class Product
    {
        public Product()
        {
            Certificates = new HashSet<Certificate>();
        }

        public int ProductId { get; set; }
        public int ProductCode { get; set; }
        public decimal Price { get; set; }
        public string Type { get; set; }
        public string SubType { get; set; }
        public int RelatedProductId { get; set; }
        public string Description { get; set; }
        public string DescriptionSpanish { get; set; }
        public string ShortDescription { get; set; }
        public string CertificateDescription { get; set; }
        public string QBItemCode { get; set; }
        public string QBDescCode { get; set; }
        public virtual ICollection<Certificate> Certificates { get; set; }
    }

Certificate.cs

public class Certificate
    {
        public int CertificateId { get; set; }
        public string Type { get; set; }
        public string Course { get; set; }
        public DateTime DateGranted { get; set; }
        public string NameOnCertificate { get; set; }
        public int FinalExamQuestionsCorrect { get; set; }
        public int FinalExamQuestionsTotal { get; set; }
        public string ClientIPAddress { get; set; }
        public int ProductCode { get; set; }
        public virtual Product Product { get; set; }
        public string UserId { get; set; }
        public virtual ApplicationUser ApplicationUser { get; set; }
    }

ProductConfiguration.cs

public class ProductConfiguration : EntityTypeConfiguration<Product>
    {
        public ProductConfiguration()
        {
            HasKey(p => p.ProductId);

            Property(p => p.ProductId)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);


            Property(p => p.ProductCode)
                 .HasColumnAnnotation("Index",
                    new IndexAnnotation(new IndexAttribute("AK_Product_ProductCode") { IsUnique = true }));


        }
    }

CertificateConfiguration.cs

public class CertificateConfiguration : EntityTypeConfiguration<Certificate>
    {
        public CertificateConfiguration()
        {
            HasKey(c => c.CertificateId);

            Property(c => c.CertificateId)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);



            Property(p => p.ProductCode).HasColumnName("ProductId");


            HasRequired(c => c.ApplicationUser)
               .WithMany(u => u.Certificates)
               .HasForeignKey(c => c.UserId);

            HasRequired(c => c.Product)
               .WithMany(p => p.Certificates)
               .HasForeignKey(c => c.ProductCode);
        }
    }

Notice, I created a Foreign Key ProductCode (rename it to ProductId) in Certificate Table.

I also seeded Product table with some data in which the ProductId generated by database and ProductCode that I manually defined. The problem is when I am trying to insert a record in Certificate table by running a sql query in which I defined a foreign key that's actually a ProductCode (not ProductId), it throws an error

SQL QUERY:

Insert into [dbo].[Certificates]
values (
'TestType','TestCourse',GETUTCDATE(),'TestName',1,5,
'127.0.0.1',201,'userId'
)

201 is the ProductCode in query that exist in Product table

Error

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Certificates_dbo.Products_ProductId". The conflict occurred in database "myDatabse", table "dbo.Products", column 'ProductId'.

I am not sure why it's looking for Primary Key ProductId. It should look for the ProductCode in Product table weather it exist or not.

You said you're trying to insert into the Certificate table with a sql query ... could you post a sample of that? — Peter, Aug 14 '15 at 16:31

score 0 · Answer 1 · edited May 23 '17 at 11:51

I believe you're running into a limitation of EF whereby your FK on the dependent end of the relationship has to refer back to the PK on the principal end. When you configure the relationship between Product and Certificates here:

HasRequired(c => c.Product)
  .WithMany(p => p.Certificates)
  .HasForeignKey(c => c.ProductCode);

this establishes the foreign key relationship between the Product primary key - Product.ProductId - and the foreign key you defined - Certificate.ProductCode (named Certificate.ProductId in your table).

While the database will support FK referring back to a unique key in your table, EF does not support it (at this time).

Here are a few other SO questions that cover this issue:

How to get EF6 to honor Unique Constraint (on FK) in Association/Relationship multiplicity?

Database first Entity Framework mapping unique foreign keys as one to many

And here's the official feature request to add this feature to EF:

Unique Constraint (i.e. Candidate Key) Support

According to the comments, it looks like they are working adding this in EF7.

So, There isn't a way to establish relationship between `Product.ProductCode` and `Certificate.ProductCode` (named `Certificate.ProductId` ? — Ammar Khan, Aug 14 '15 at 16:53
Not that I'm aware of. You might be able to fake it by making `Product.ProductCode` your primary key, but this may have consequences for other relationships you may need to define. — Peter, Aug 14 '15 at 16:57

How to Insert data in table when Foreign Key is not a primary key

1 Answers1