Home > Software design >  ASP.NET: The INSERT statement conflicted with the FOREIGN KEY constraint
ASP.NET: The INSERT statement conflicted with the FOREIGN KEY constraint

Time:12-10

Greetings I am new in ASP.NET core and I have problem creating database with relationships Customers could be many addresses(one to many relationship). Addresses only one country of origin(one to one relationship)

I tried make one to many relationship without one to one relationship and it works same for opposite. I just do not get it where is the problem with FK key in my db context if I am trying make them both. Error -

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Address_Customer_CustomerId". The conflict occurred in database "AdressesContext-da27b4d1-a732-4121-bb76-e3bd5633718f", table "dbo.Customer", column 'Id'.

My entities

namespace Adresses.Models
{
    public enum Gender
    {
        Female,Male
    }
    public class Customer
    {
        public int Id { get; set; }
        public string FullName { get; set; }
        public string Email { get; set; }
        public DateTime Birthdate { get; set; }
        public Gender Gender { get; set; }

        public ICollection<Address> Addresses { get; set; }
    }
}
Country model

namespace Adresses.Models
{
    public class Country
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

namespace Adresses.Models
{
    public enum Type
    {
        Billing,Delivery
    }
    public class Address
    {
        public int Id { get; set; }
        public int CustomerId { get; set; }
        public int CountryId { get; set; }
        public string StreetAddress { get; set; }
        public string City { get; set; }
        public string Zip { get; set; }
        public Type Type { get; set; }


        public Customer Customer { get; set; }
        public Country Country { get; set; }
    }
}

Seeding database I am using this method

using Adresses.Models;
using System;
using System.Linq;
namespace Adresses.Data
{
    public class DbInitializer
    {
        public static void Initialize(AdressesContext context)
        {
            context.Database.EnsureCreated();

            // Look for any students.
            if (context.Customer.Any())
            {
                return;   // DB has been seeded
            }

            var customers = new Customer[]
            {
            new Customer{FullName="Carson Alexander",Birthdate=DateTime.Parse("2005-09-01"),Gender=Gender.Female,Email="[email protected]"},
            new Customer{FullName="Meredith Alonso",Birthdate=DateTime.Parse("2002-09-01"),Gender=Gender.Male,Email="[email protected]"}
            //new Customer{FullName="Arturo Anand",Birthdate=DateTime.Parse("2003-09-01"),Gender=Gender.Female,Email="[email protected]"},
            //new Customer{FullName="Gytis Barzdukas",Birthdate=DateTime.Parse("2002-09-01"),Gender=Gender.Male,Email="[email protected]"},
            //new Customer{FullName="Yan Li",Birthdate=DateTime.Parse("2002-09-01"),Gender=Gender.Female,Email="[email protected]"},
            //new Customer{FullName="Peggy Justice",Birthdate=DateTime.Parse("2001-09-01"),Gender=Gender.Male,Email="[email protected]"},
            //new Customer{FullName="Laura Norman",Birthdate=DateTime.Parse("2003-09-01"),Gender=Gender.Female,Email="[email protected]"},
            //new Customer{FullName="Nino Olivetto",Birthdate=DateTime.Parse("2005-09-01"),Gender=Gender.Female,Email="[email protected]"}
            };
            foreach (Customer s in customers)
            {
                context.Customer.Add(s);
            }
            context.SaveChanges();

            var countries = new Country[]
          {
            new Country{Id=1,Name="Heavens"},
            new Country{Id=2,Name="Hell"}
          };

            foreach (Country e in countries)
            {
                context.Country.Add(e);
            }
            context.SaveChanges();

            var addresses = new Address[]
            {
            new Address{CustomerId=1,StreetAddress="test",City="One City",Zip="2402",Type=Models.Type.Delivery,CountryId=1},
            new Address{CustomerId=2,StreetAddress="another",City="City",Zip="2403",Type=Models.Type.Billing,CountryId=2},
            new Address{CustomerId=2,StreetAddress="another",City="City",Zip="2403",Type=Models.Type.Delivery,CountryId=1}
            
            };
            foreach (Address e in addresses)
            {
                context.Address.Add(e);
            }
            context.SaveChanges();

          

        }
    }
}

CodePudding user response:

you have to fix the country relations

 public class Country
    {
        public int Id { get; set; }
        public string Name { get; set; }
        
       public ICollection<Address> Addresses { get; set; }
    }

and since you don't know customerId it is better to start adding from country and address. This way db server will automatically assign primary and foreign keys.

var addresses = new Address[]
{
   new Address{StreetAddress="test",City="One City",Zip="2402",Type=Models.Type.Delivery,
Country =   new Country{ Name="Heavens"},
Customer =   new Customer {FullName="Carson Alexander",Birthdate=DateTime.Parse("2005-09-01"),Gender=Gender.Female,Email="[email protected]"}
},
 new Address{StreetAddress="another",City="City",Zip="2403",Type=Models.Type.Delivery,
Country=  new Country { Name="Hell"},
Customer= new Customer { FullName="Meredith Alonso",Birthdate=DateTime.Parse("2002-09-01"),Gender=Gender.Male,Email="[email protected]"}
}
};

 context.Address.AddRange(addresses);
 context.SaveChanges();

CodePudding user response:

The customers you're inserting aren't being given the IDs 1 and 2, so your addresses have the wrong CustomerId values.

It's not entirely clear why that's happening - have you previously inserted and deleted records from the customers table?

It's generally easier to insert the addresses as part of the customer:

var countries = new Country[]
{
    new Country{ Name="Heavens" },
    new Country{ Name="Hell" }
};

foreach (Country e in countries)
{
    context.Country.Add(e);
}

var customers = new Customer[]
{
    new Customer
    {
        FullName = "Carson Alexander",
        Birthdate = new DateTime(2005, 9, 1),
        Gender = Gender.Female,
        Email = "[email protected]",
        Addresses = new[]
        {
            new Address
            {
                StreetAddress = "test",
                City = "One City",
                Zip = "2402",
                Type = Models.Type.Delivery,
                Country = countries[0]
            }
        }
    },
    new Customer
    {
        FullName = "Meredith Alonso",
        Birthdate = new DateTime(2002, 9, 1),
        Gender = Gender.Male,
        Email = "[email protected]",
        Addresses = new[]
        {
            new Address
            {
                StreetAddress = "another",
                City = "City",
                Zip = "2403",
                Type = Models.Type.Billing,
                Country = countries[1]
            },
            new Address
            {
                StreetAddress = "another",
                City = "City",
                Zip = "2403",
                Type = Models.Type.Delivery,
                Country = countries[0]
            }
        }
    }
};

foreach (Customer s in customers)
{
    context.Customer.Add(s);
}

context.SaveChanges();
  • Related