Q:

Getting foreign key error on updation of database in asp.net mvc

I am trying to establish simple one to many and many to many relationships and inserting records in tables. But whenever I update the db, I get this error -

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Movies_dbo.Producers_ProducerId". The conflict occurred in database "aspnet-MvcApplication1-20140713210255", table "dbo.Producers", column 'ProducerId'.

Here are my model classes - 
Movie.cs
public class Movie
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int MovieId { get; set; }
    public string Name { get; set; }
    public string YearOfRelease { get; set; }
    public string Plot { get; set; }
    public string ImgUrl { get; set; }

    public int ProducerId { get; set; }

    public virtual Producer Producer { get; set; }
    public virtual ICollection<Actor> Actors { get; set; }
    public virtual ICollection<MovieReview> Reviews { get; set; }

}

Actor.cs
public class Actor
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ActorId { get; set; }
    public string Name { get; set; }
    public string Sex { get; set; }
    public string DOB { get; set; }
    public string BioData { get; set; }

    public virtual ICollection<Movie> Movies { get; set; }

    [Display(Name = "User Name")]
    [DisplayFormat(NullDisplayText = "anonymous")]
    public virtual UserProfile ApplicationUser { get; set; }

}

Producer.cs
public class Producer
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ProducerId { get; set; }
    public string Name { get; set; }
    public string Sex { get; set; }
    public string DOB { get; set; }
    public string BioData { get; set; }

    public virtual ICollection<Movie> Movies { get; set; }

    [Display(Name = "User Name")]
    [DisplayFormat(NullDisplayText = "anonymous")]
    public virtual UserProfile ApplicationUser { get; set; }
}

and here is my configuration file - 
context.Movies.AddOrUpdate(
            p => p.Name,
            new Movie { Name = "Shawshank Redemption", Plot = "Wrongly convicted for the crime he didn't commit, finds redemption and more in the jail.", YearOfRelease = "1994-5-23", Actors = new List<Actor>()},
            new Movie { Name = "Saving Private Ryan", Plot = "Finding a lost war man.", YearOfRelease = "1998-2-11", Actors = new List<Actor>()}
            );
        context.SaveChanges();

        context.Actors.AddOrUpdate(
            p => p.Name,
            new Actor { Name = "Tim Robbins", Sex = "Male" },
            new Actor { Name = "Tom Hanks", Sex = "Male" }
            );
        context.SaveChanges();

        context.Producers.AddOrUpdate(
            p => p.Name,
            new Producer { Name = "Niki Marvin" },
            new Producer { Name = "Steven Spielberg" }
            );
        context.SaveChanges();

        var movie = context.Movies.SingleOrDefault(m => m.Name == "Shawshank Redemption");
        var actor = context.Actors.SingleOrDefault(a => a.Name == "Tim Robbins");
        movie.Actors.Add(actor);
        var producer = context.Producers.SingleOrDefault(p => p.Name == "Niki Marvin");
        movie.Producer = producer;

        movie = context.Movies.SingleOrDefault(m => m.Name == "Saving Private Ryan");
        actor = context.Actors.SingleOrDefault(a => a.Name == "Tom Hanks");
        movie.Actors.Add(actor);
        producer = context.Producers.SingleOrDefault(p => p.Name == "Steven Spielberg");
        movie.Producer = producer;

        context.SaveChanges();

What am I doing wrong here? Is the insertion of data correct above? Any kind of help will be appreciated.

A:

When you insert or update a Movie you need to set a valid ProducerId which is an existent ProducerId in your Producers table. If you pass a value which does not exist in the table, then your insert or update will be unsuccessful due to the fact that the value does not exist in the referenced table.
If a movie does not have to have a producer, then you can set the ProducerId of your Movies table/model to be nullable both in the database scheme and your Movie model.

