Cannot insert the value NULL into column error message

Question

I am trying to run a migration. The migration goes through, however, when I try to update the database I get this error message

Cannot insert the value NULL into column 'scheduleDateAndTimeid', table 'aspnet-ScheduleWebApp-20190525082521.dbo.Students'; column does not allow nulls. UPDATE fails.

I have two model classes, one called student and another called scheduleDateAndTime.

I created a DTO with mapping on the student model and added a navigation property to the scheduleDateAndTime

Migration

public partial class AnotherMigrationTwoTWOTwotwo : DbMigration
{
    public override void Up()
    {
        DropForeignKey("dbo.Students", "scheduleDateAndTime_id", "dbo.ScheduleDateAndTimes");
        DropIndex("dbo.Students", new[] { "scheduleDateAndTime_id" });
        RenameColumn(table: "dbo.Students", name: "scheduleDateAndTime_id", newName: "scheduleDateAndTimeid");
        AlterColumn("dbo.Students", "scheduleDateAndTimeid", c => c.Int(nullable: false));
        CreateIndex("dbo.Students", "scheduleDateAndTimeid");
        AddForeignKey("dbo.Students", "scheduleDateAndTimeid", "dbo.ScheduleDateAndTimes", "id", cascadeDelete: true);
    }

    public override void Down()
    {
        DropForeignKey("dbo.Students", "scheduleDateAndTimeid", "dbo.ScheduleDateAndTimes");
        DropIndex("dbo.Students", new[] { "scheduleDateAndTimeid" });
        AlterColumn("dbo.Students", "scheduleDateAndTimeid", c => c.Int());
        RenameColumn(table: "dbo.Students", name: "scheduleDateAndTimeid", newName: "scheduleDateAndTime_id");
        CreateIndex("dbo.Students", "scheduleDateAndTime_id");
        AddForeignKey("dbo.Students", "scheduleDateAndTime_id", "dbo.ScheduleDateAndTimes", "id");
    }
}

StudentDto model class:

public class StudentDto
{
    public int id { get; set; }
    public string name { get; set; }
    public byte age { get; set; }
    public string subjectStudying { get; set; }
    public string disability { get; set; }
    public string additionalInformation { get; set; }
    public ScheduleDateAndTimeDto ScheduleDateAndTime { get; set; }
    public int scheduleDateAndTimeid { get; set; }
}

Answer 1

You're trying to update a table by making a nullable foreign key a non-nullable foreign key: if you're operating with an empty table (no records) this operation will just work fine, but if there are existing records the migration could encounter NULL values in those records for that column (which was a valid value BEFORE the migration).

This could sound like a limit but makes absolutely sense: if you’re updating the column to be not-nullable, how should a migration deal with existing data? (think about production environment releases, etc..)

A work-around could be to first keep the column nullable, then update all the NULL values for the existing records and then make it not-nullable.