Entity Framework Migration - Alter Table Statement Conflicted with Foreign Key Constraint

Question

I am creating a blog of sorts with C#/.NET 4.5 framework using EF code-first migrations.

Things have been going well up until adding a third relationship into my main class.

I have a "story" class (kind of like a "post" for a blog) where I have the author as the user who's logged in (set in the controller), a title, some content, the date it was created, and a genre and type of story.

public class Story
    {
        public int Id { get; set; }

        public string AuthorId { get; set; }
        public virtual ApplicationUser Author { get; set; }

        [Required]
        [StringLength(50)]
        public string Title { get; set; }

        [Required]
        [MinLength(100), MaxLength(5000)]
        public string Content { get; set; }

        [Required]
        public int GenreId { get; set; }
        public Genre Genre { get; set; }

        [Required]
        public int StoryTypeId { get; set; }
        public StoryType StoryType { get; set; }

        public DateTime CreatedAt { get; set; }
    }

I added storytypes as a property to the story. StoryType links to the StoryType model:

public class StoryType
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

I made sure to add my dbset to my application db context:

public DbSet<Genre> Genres { get; set; }
public DbSet<Story> Stories { get; set; }
public DbSet<StoryType> StoryTypes { get; set; }

I pretty much followed the same steps I used to create the relationship between the story and genre (which worked fine). Before I start building the StoryType controllers, I went into package-console and ran:

add-migration

that returned:

 public partial class CreateTypeTable : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.StoryTypes",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        Name = c.String(),
                    })
                .PrimaryKey(t => t.Id);

            AddColumn("dbo.Stories", "StoryTypeId", c => c.Int(nullable: false));
            CreateIndex("dbo.Stories", "StoryTypeId");
            AddForeignKey("dbo.Stories", "StoryTypeId", "dbo.StoryTypes", "Id", cascadeDelete: true);
        }

        public override void Down()
        {
            DropForeignKey("dbo.Stories", "StoryTypeId", "dbo.StoryTypes");
            DropIndex("dbo.Stories", new[] { "StoryTypeId" });
            DropColumn("dbo.Stories", "StoryTypeId");
            DropTable("dbo.StoryTypes");
        }
    }

Glancing over it, I didn't see an issue, then ran:

update-database

in package-console.

Which returned:

Error Number:547,State:0,Class:16
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Stories_dbo.StoryTypes_StoryTypeId". The conflict occurred in database "aspnet-HiRatik.Stories-20180724043630", table "dbo.StoryTypes", column 'Id'.

I'm not sure what went wrong here. I did the same process with the Genre relationship and it worked. I didn't see a difference in the two.

Answer 1

because the StoryTypeId in the class Story dosen't accept null so you need to make the StoryTypeId nullable :

public class Story
{
    public int Id { get; set; }

    public string AuthorId { get; set; }
    public virtual ApplicationUser Author { get; set; }

    [Required]
    [StringLength(50)]
    public string Title { get; set; }

    [Required]
    [MinLength(100), MaxLength(5000)]
    public string Content { get; set; }

    [Required]
    public int GenreId { get; set; }
    public Genre Genre { get; set; }

    public int? StoryTypeId { get; set; }
    public StoryType StoryType { get; set; }

    public DateTime CreatedAt { get; set; }
}

or you create first the table StoryType and you add elements to it and then add the StoryTypeId with default value:

public class Story
{
    public int Id { get; set; }

    public string AuthorId { get; set; }
    public virtual ApplicationUser Author { get; set; }

    [Required]
    [StringLength(50)]
    public string Title { get; set; }

    [Required]
    [MinLength(100), MaxLength(5000)]
    public string Content { get; set; }

    [Required]
    public int GenreId { get; set; }
    public Genre Genre { get; set; }

    [[DefaultValue(1)]]
    public int StoryTypeId { get; set; }
    public StoryType StoryType { get; set; }

    public DateTime CreatedAt { get; set; }
}

in this case you must update the database after creating StoryType and the after adding the StoryTypeId to the class Story