How can I seed my database with a foreign key?

Question

I get the following error when i try to seed my database.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Products_Suppliers".The conflict occured in database "Kungu", table "dbo.Suppliers", column 'SupplierId'. The statement has been terminated

the entities classes are Supplier and Product.

Supplier Entity

namespace Kungu.Domain.Entities.Shop
{
    
        public partial class Supplier
        {
            public Supplier()
            {
            

            Products = new HashSet<Product>();
            
            }
            [Key]
            public int SupplierId { get; set; }

            [StringLength(40)]
            public string CompanyName { get; set; } = null!;
            [StringLength(30)]
            public string? ContactName { get; set; }
            [StringLength(30)]
            public string? ContactTitle { get; set; }
            [StringLength(60)]
            public string? Address { get; set; }
             [StringLength(15)]
            public string? City { get; set; }
             [StringLength(15)]
            public string? Region { get; set; }
            [StringLength(10)]
            public string? PostalCode { get; set; }
            [StringLength(15)]
            public string? Country { get; set; }
            [StringLength(24)]
            public string? Phone { get; set; }
            [Column(TypeName = "ntext")]
            public string? HomePage { get; set; }
        
        public int AffiliationId { get; set; }

        
        [InverseProperty("Supplier")]
        public virtual ICollection<Product> Products { get; set; }
        
        public virtual Affiliations? Affiliation { get; set; }
    }
}

Product Entity

namespace Kungu.Domain.Entities.Shop
{
    
        
        public partial class Product
        {
            public Product()
            {
            //Suppliers = new HashSet<Supplier>();

            OrderDetails = new HashSet<OrderDetail>();
            BookingDetails = new HashSet<BookingDetails>();
            PackagesProductsSuppliers = new HashSet<PackagesProductsSuppliers>();
            }

            [Key]
            public int ProductId { get; set; }
            [StringLength(40)]
            public string ProductName { get; set; } = null!;
            public string? Description { get; set; }
            public string? ImageURL { get; set; }
            public int SupplierId { get; set; }
            public int CategoryId { get; set; }
            [StringLength(20)]
            public string? QuantityPerUnit { get; set; }
            [Column(TypeName = "money")]
            public decimal Price { get; set; }
        
            public int Quantity { get; set; }
            
            public short? UnitsInStock { get; set; }
            public short? UnitsOnOrder { get; set; }
            public short? ReorderLevel { get; set; }
            public bool Discontinued { get; set; }

            [ForeignKey("CategoryId")]
            [InverseProperty("Products")]
            
            public virtual Category? Category  { get; set; }

            [ForeignKey("SupplierId")]
            [InverseProperty("Products")]
            public virtual Supplier? Supplier { get; set; }
            [InverseProperty("Product")]
            public virtual ICollection<OrderDetail> OrderDetails {get;set;}
            //public virtual ICollection<Supplier> Suppliers { get; set; }
            public virtual ICollection<BookingDetails> BookingDetails { get; set; }
            public virtual ICollection<PackagesProductsSuppliers> PackagesProductsSuppliers { get; set; }


    }
}

What am i doing wrong?

this is how i am seeding the database at first i forgot to add the FK SupplierId but after adding it their is stiil no change.

 protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            
            base.OnModelCreating(modelBuilder);

            //Products
            //Beauty Category
            modelBuilder.Entity<Product>().HasData(new Product
            {
                ProductId = 1,
                ProductName = "Glossier - Beauty Kit",
                Description = "A kit provided by Glossier, containing skin care, hair care and makeup products",
                ImageURL = "/Images/Beauty/Beauty1.png",
                Quantity = 100,
                SupplierId = 1,
                CategoryId = 1,
                QuantityPerUnit = "1 box",
                Price = 100

            });
            modelBuilder.Entity<Product>().HasData(new Product
            {
                ProductId = 2,
                ProductName = "Curology - Skin Care Kit",
                Description = "A kit provided by Curology, containing skin care products",
                ImageURL = "/Images/Beauty/Beauty2.png",
                Quantity = 45,
                SupplierId = 1,
                CategoryId = 1,
                QuantityPerUnit = "1 box",
                Price = 50

The error means exactly what it says, you're violating a foreign key constraint. In other words, you're trying to insert values into a table with a foreign key constraint and no such foreign key exists in the referenced table. Have you checked that all the IDs you're referencing actually exist in the database? For example, is there actually a `Supplier` with `ID = 1` present in your database? — madmonk46, Nov 04 '22 at 11:36
@madmonk46 has hit the nail on the head, I think. You are trying to add entities that link to a Supplier and Category but you haven't loaded the relevant records for Supplier or Category to the database. — David Brunning, Nov 04 '22 at 16:55
@madmonk46 is right. It seems like there is no row with ID=1 in the Suppliers table. You should first seed the Suppliers table, then use its primary keys for adding products with SupplierId foreign keys. — developer-partners, Aug 17 '23 at 08:10

score -1 · Answer 1 · answered Nov 04 '22 at 11:46

Everything looks fine. However, it is normal to get an error when you try to save a product. because of the SupplierId conflicts. To solve this problem, you can create a trigger in the database section and provide an automatic increase (sequence).

Oracle 12c adds support for autoincrementing sequences example : How to create id with AUTO_INCREMENT on Oracle?

 CREATE TABLE MAPS
 (
   MAP_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
   MAP_NAME VARCHAR(24) NOT NULL,
   UNIQUE (MAP_ID, MAP_NAME)
 );


-- create table
CREATE TABLE MAPS
(
  MAP_ID INTEGER NOT NULL ,
  MAP_NAME VARCHAR(24) NOT NULL,
  UNIQUE (MAP_ID, MAP_NAME)
);

-- create sequence
CREATE SEQUENCE MAPS_SEQ;

 -- create tigger using the sequence
CREATE OR REPLACE TRIGGER MAPS_TRG 
BEFORE INSERT ON MAPS 
FOR EACH ROW
WHEN (new.MAP_ID IS NULL)
BEGIN
 SELECT MAPS_SEQ.NEXTVAL
 INTO   :new.MAP_ID
 FROM   dual;
END;
/

This question is about how Entity Framework is used to manage a database structure in a code-first world. Using explicit T- SQL script isn't going to help. The Supplier entity can have an auto-incrementing identity field but in EF that would be set up using annotations on the code class like [DatabaseGenerated(DatabaseGeneratedOption.Identity)] — David Brunning, Nov 04 '22 at 16:57

How can I seed my database with a foreign key?

1 Answers1