title: Entity Framework - Foreign Key Constraint
body: <p>I have 3 tables in a database:</p>
  
  <p><strong>Song</strong> (ID, Title, ReleaseDate)<br>
  <strong>Album</strong> (ID, Title, ReleaseDate)<br>
  <strong>Artist</strong> (ID, FirstName, LastName)    </p>
  
  <p>I have a Related table so that song can be related to an Album or an Artist, or both:</p>
  
  <p><strong>RelatedSong</strong> (ID, ParentID, SongID, TrackNumber) (Foreign Key on Album.ID and Artist.ID for ParentID and obviously Song.ID for SongID)  </p>
  
  <p>So, using these four tables, I expected Entity Framework to generate models that would allow me to simply execute and run in my MVC project, but it fails upon saving due to a Foreign Key Constraint.  If I set the ParentID = Album.ID, then it complains that Artist.ID is NULL, and vise-versa.  Any suggestions?  I am rewriting the front-end for an existing application so the database can't change.  I need to know how the model needs to be constructed so that this works.  It's either in the model or the modelBuilder (Fluent API).</p>
  
  <p>Album Model:</p>
  
  <pre><code>[Table("Album")]
  public partial class Album
  {
      public Album()
      {
          RelatedAlbums = new HashSet&lt;RelatedAlbum&gt;();
          RelatedSongs = new HashSet&lt;RelatedSong&gt;();
      }
  
      public Guid ID { get; set; }
  
      [Required]
      public string Title { get; set; }
  
      public DateTime ReleaseDate { get; set; }
  
      public virtual ICollection&lt;RelatedAlbum&gt; RelatedAlbums { get; set; }
  
      public virtual ICollection&lt;RelatedSong&gt; RelatedSongs { get; set; }
  }
  </code></pre>
  
  <p>Artist Model:</p>
  
  <pre><code>[Table("Artist")]
  public partial class Artist
  {
      public Artist()
      {
          RelatedAlbums = new HashSet&lt;RelatedAlbum&gt;();
          RelatedSongs = new HashSet&lt;RelatedSong&gt;();
      }
  
      public Guid ID { get; set; }
  
      public string FirstName { get; set; }
  
      [Required]
      public string LastName { get; set; }
  
      public virtual ICollection&lt;RelatedAlbum&gt; RelatedAlbums { get; set; }
  
      public virtual ICollection&lt;RelatedSong&gt; RelatedSongs { get; set; }
  }
  </code></pre>
  
  <p>Related Album:</p>
  
  <pre><code>[Table("RelatedAlbum")]
  public partial class RelatedAlbum
  {
      public Guid ID { get; set; }
  
      public Guid ParentID { get; set; }
  
      public Guid AlbumID { get; set; }
  
      public virtual Album Album { get; set; }
  
      public virtual Artist Artist { get; set; }
  }
  </code></pre>
  
  <p>Related Song:</p>
  
  <pre><code>[Table("RelatedSong")]
  public partial class RelatedSong
  {
      public Guid ID { get; set; }
  
      public Guid ParentID { get; set; }
  
      public Guid SongID { get; set; }
  
      public int? TrackNumber { get; set; }
  
      public virtual Album Album { get; set; }
  
      public virtual Artist Artist { get; set; }
  
      public virtual Song Song { get; set; }
  }  
  </code></pre>
  
  <p>Song:</p>
  
  <pre><code>[Table("Song")]
  public partial class Song
  {
      public Song()
      {
          RelatedSongs = new HashSet&lt;RelatedSong&gt;();
      }
  
      public Guid ID { get; set; }
  
      [Required]
      public string Title { get; set; }
  
      public DateTime ReleaseDate { get; set; }
  
      public virtual ICollection&lt;RelatedSong&gt; RelatedSongs { get; set; }
  }
  </code></pre>
  
  <p>DbContext:</p>
  
  <pre><code>public partial class MusicDbContext : DbContext
  {
      public MusicDbContext()
          : base("name=MusicDbContext")
      {
      }
  
      public virtual DbSet&lt;Album&gt; Albums { get; set; }
      public virtual DbSet&lt;Artist&gt; Artists { get; set; }
      public virtual DbSet&lt;RelatedAlbum&gt; RelatedAlbums { get; set; }
      public virtual DbSet&lt;RelatedSong&gt; RelatedSongs { get; set; }
      public virtual DbSet&lt;Song&gt; Songs { get; set; }
  
      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
          modelBuilder.Entity&lt;Album&gt;()
              .HasMany(e =&gt; e.RelatedAlbums)
              .WithRequired(e =&gt; e.Album)
              .WillCascadeOnDelete(false);
  
          modelBuilder.Entity&lt;Album&gt;()
              .HasMany(e =&gt; e.RelatedSongs)
              .WithRequired(e =&gt; e.Album)
              .HasForeignKey(e =&gt; e.ParentID)
              .WillCascadeOnDelete(false);
  
          modelBuilder.Entity&lt;Artist&gt;()
              .HasMany(e =&gt; e.RelatedAlbums)
              .WithRequired(e =&gt; e.Artist)
              .HasForeignKey(e =&gt; e.ParentID)
              .WillCascadeOnDelete(false);
  
          modelBuilder.Entity&lt;Artist&gt;()
              .HasMany(e =&gt; e.RelatedSongs)
              .WithRequired(e =&gt; e.Artist)
              .HasForeignKey(e =&gt; e.ParentID)
              .WillCascadeOnDelete(false);
  
          modelBuilder.Entity&lt;Song&gt;()
              .HasMany(e =&gt; e.RelatedSongs)
              .WithRequired(e =&gt; e.Song)
              .WillCascadeOnDelete(false);
      }
  }
  </code></pre>
  
  <p><strong>UPDATE:</strong><br>
  Below is the controller code for the Create method.  </p>
  
  <pre><code>[HttpPost]
  [ValidateAntiForgeryToken]
  public ActionResult Create([Bind(Include = "ID,ParentID,SongID,TrackNumber")] RelatedSong relatedSong)
  {
      if (ModelState.IsValid)
      {
          relatedSong.ID = Guid.NewGuid();
          db.RelatedSongs.Add(relatedSong);
          db.SaveChanges();
          return RedirectToAction("Index");
      }
  
      ViewBag.ParentID = new SelectList(db.Albums, "ID", "Title", relatedSong.ParentID);
      ViewBag.SongID = new SelectList(db.Songs, "ID", "Title", relatedSong.SongID);
  
      return View(relatedSong);
  }
  </code></pre>
  
  <p><strong>UPDATE 2:</strong><br>
  Maybe the database model is not correct or something?  Not sure why this wouldn't be possible because it seems to me like this is the most efficient way to relate data to more than one "parent".  I just read another post that says that it's not possible (but why would the database designer allow me to do this?)...  </p>
  
  <p>See: <a href="https://stackoverflow.com/questions/1186931/multiple-foreign-keys-to-a-single-column">Multiple foreign keys to a single column</a></p>