Hey there,

I've got a scenario where I'm trying to get multiple foreign keys tied into the same table. Scaffolding is failing to create the relationships as described below, but generates an exception instead.

I created a simple version that replicates the problem. The exception is:
The database creation succeeded, but the creation of the database objects did not. See InnerException for details.

The InnerException is:
Introducing FOREIGN KEY constraint 'FavoriteMovies_Drama' on table 'FavoriteMovies' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

This example is a parallel to what I'm doing. Imagine a Movie class, and then a FavoriteMovies class, which would be a profile of sorts that allows you to select from a list of Movies your fav in each category (I'm actually doing a client profiling thing, but this was easier to explain).

public class Movie
{
    public int MovieID { get; set; }
    public string Title { get; set; }
    public string Producer { get; set; }
}

public class FavoriteMovies
{
    public int FavoriteMoviesID { get; set; }

    public int ComedyFavoriteMovieID { get; set; }
    public int HorrorFavoriteMovieID { get; set; }
    public int DramaFavoriteMovieID { get; set; }
    public int SciFiFavoriteMovieID { get; set; }
    public int FlickWithHalleBerryFavoriteMovieID { get; set; }

    [ForeignKey("ComedyFavoriteMovieID")] public virtual Movie Comedy { get; set; }
    [ForeignKey("HorrorFavoriteMovieID")] public virtual Movie Horror { get; set; }
    [ForeignKey("DramaFavoriteMovieID")] public virtual Movie Drama { get; set; }
    [ForeignKey("SciFiFavoriteMovieID")] public virtual Movie SciFi { get; set; }
    [ForeignKey("FlickWithHalleBerryFavoriteMovieID")] public virtual Movie FlickWithHalleBerry { get; set; }
}

The error occurs when the code to create the database is invoked. In the database (in this example), the two tables are created as well as the Comedy key. The Drama key (which is the second...alphabetically?) fails and the app errors out. The app, on a subsequent run, allows you to move into the views but the UI is broken on the FavoriteMovies view.

I followed the walkthrough here: http://blog.stevensanderson.com/2011/01/28/mvcscaffolding-one-to-many-relationships/

Any ideas on how to wire up the 'extra' relationships?

Cheers,
James