SQLite Exception: SQLite Error 19: "Foreign Key Constraint failed"

Question

I have had a look online with this problem(SQLite EF Core - 'FOREIGN KEY constraint failed') and I have looked at previous examples where I have done this before, but I am still not able to solve this issue Here is my code for the model class of the foreign key, the DB, and the startup.

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

    public DateTime StartDateTimeBooking { get; set; }

    public DateTime TimeOfBooking { get; set; } - Potentially use this

    public DateTime EndDateTimeBooking { get; set; }

    public Doctor NameOfDoctor { get; set; }

    public PreferenceOfAttendance PreferenceOfAttendence {get; set;}

    public string DetailOfBooking { get; set; }

    //EF Dependant Relationship Appointment belongs to a patient

    public int PatientId { get; set; }

    //Navigation property
    public Patient Patient { get; set; }

    //EF Dependant Relationship Appointment belongs to a doctor
}

Services Service DB and I only included where the X occurred as well in the debug:

 public class NameOferviceDb : NameOfService
{
    private readonly DatabaseContext db;

    public NameOfServiceDb()
    {
        db = new DatabaseContext(); 
    }

    public void Initialise()
    {
       db.Initialise(); 
    }

  public AppointmentBooking AddAppointmentBooking(AppointmentBooking ab)
    {
        var existing = GetPatientById(ab.Id);

        if (existing != null)
        {
            return null;
        }
        else
        {
            var booking = new AppointmentBooking
            {
                PatientId = ab.Id,
                StartDateTimeBooking = ab.StartDateTimeBooking,
                NameOfDoctor = ab.NameOfDoctor,
                PreferenceOfAttendence = ab.PreferenceOfAttendence,
                DetailOfBooking = ab.DetailOfBooking,

            };

            db.Bookings.Add(booking); //Add new booking to the db
            db.SaveChanges(); //Savechanges and return to the db 

            return booking; // return booking 
        }
    }

The Startup.cs

  public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public IConfiguration Configuration { get; }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {   
        
        // ** Add Cookie Authentication via extension method **
        //services.AddCookieAuthentication();

        // ** Add Cookie and Jwt Authentication via extension method **
        services.AddCookieAndJwtAuthentication(Configuration);

        // ** Enable Cors for and webapi endpoints provided **
        services.AddCors();
        
        // Add UserService to DI - change to use real UserService           
        services.AddTransient<NameOfService,NameOfServiceDb>();

        // ** Required to enable asp-authorize Taghelper **            
        services.AddSingleton<IHttpContextAccessor, HttpContextAccessor>();
        
        services.AddControllersWithViews();
    }

    // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
    public void Configure(IApplicationBuilder app, IWebHostEnvironment env, IServiceProvider provider)
    {
        if (env.IsDevelopment())
        {
            //app.UseDeveloperExceptionPage();


            // seed users - using service provider to get UserService from DI
            Seeder.Seed(provider.GetService<INameOfService>());
        }
        else
        {
            app.UseExceptionHandler("/Home/Error");
            // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
            app.UseHsts();
        }

        app.UseHttpsRedirection();
        app.UseStaticFiles();

        app.UseRouting();

        // ** configure cors to allow full cross origin access to any webapi end points **
        app.UseCors(c => c.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());

        // ** turn on authentication/authorisation **
        app.UseAuthentication();
        app.UseAuthorization();

        app.UseEndpoints(endpoints =>
        {
            endpoints.MapControllerRoute(
                name: "default",
                pattern: "{controller=Home}/{action=Index}/{id?}");
        });
    }
}

The database context

public class DatabaseContext :DbContext
{
    public DbSet<Patient> Patients { get; set; }

    public DbSet<AppointmentBooking> Bookings { get; set; }
   
    public DbSet<User> Users { get; set; }


    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder                  
            .UseSqlite("Filename=data.db")
            //.LogTo(Console.WriteLine, LogLevel.Information) // remove in production
            //.EnableSensitiveDataLogging()                   // remove in production
            ;
    }

    public void Initialise()
    {
        Database.EnsureDeleted();
        Database.EnsureCreated();
    }

}

Answer 1

The problem is that you assign PatientId = ab.Id, but I am sure that ab.Id = 0 and there is no patient with this Id. This is why it throws exception. Try this

       
var existingPatient =  db.Patients.Any(b=> b.PatientId==ab.PatientId);

        if (!existingPatient)
        {
            return null;
        }      

            var booking = new AppointmentBooking
            {
                PatientId = ab.PatientId,
                .....
            };

            db.Bookings.Add(booking); //Add new booking to the db
            db.SaveChanges(); //Savechanges and return to the db 
            return booking;