'SQLite Error 19: 'FOREIGN KEY constraint failed'.' while try to insert data

Question

I am trying to run my database code, but I keep getting this error and I've done whatever I can think of to fix it, whether it is random posts, or chatgpt, but I can't seem to figure it out. The comments are in Swedish so you can ignore them.

Error says it stems from this line of code:

await db.Database.MigrateAsync();

Error details:

Microsoft.EntityFrameworkCore.DbUpdateException
  HResult=0x80131500
  Message=An error occurred while saving the entity changes. See the inner exception for details.
  Source=Microsoft.EntityFrameworkCore.Relational
  StackTrace:
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync>d__50.MoveNext()
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__9.MoveNext()
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__9.MoveNext()
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__9.MoveNext()
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.<SaveChangesAsync>d__8.MoveNext()
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__113.MoveNext()
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__117.MoveNext()
   at Microsoft.EntityFrameworkCore.DbContext.<SaveChangesAsync>d__63.MoveNext()
   at Microsoft.EntityFrameworkCore.DbContext.<SaveChangesAsync>d__63.MoveNext()
   at Program.<<Main>$>d__0.MoveNext() in [redacted]\Program.cs:line 39

  This exception was originally thrown at this call stack:
    Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(int, SQLitePCL.sqlite3)
    Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
    Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(System.Data.CommandBehavior)
    Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(System.Data.CommandBehavior, System.Threading.CancellationToken)
    Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(System.Data.CommandBehavior, System.Threading.CancellationToken)
    Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(Microsoft.EntityFrameworkCore.Storage.RelationalCommandParameterObject, System.Threading.CancellationToken)
    Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(Microsoft.EntityFrameworkCore.Storage.RelationalCommandParameterObject, System.Threading.CancellationToken)
    Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(Microsoft.EntityFrameworkCore.Storage.IRelationalConnection, System.Threading.CancellationToken)

Inner Exception 1:
SqliteException: SQLite Error 19: 'FOREIGN KEY constraint failed'.

The entire Program.cs and other code is shown here:

using EF_Core___Migrations___Seeding;
using Microsoft.EntityFrameworkCore;
using System.Runtime.InteropServices;

Console.WriteLine("DB: " + Path.Combine(AppContext.BaseDirectory, "shop.db"));

// Säkerställ DB + migrations + Seed
using(var db = new ShopContext())
{
    // Migrate Async: Skapar databasen om det inte fnns
    // Kör bara om det inte finns några kategorier sen innan

    await db.Database.MigrateAsync();

    // Enkel seeding för databasen
    // Kör bara om det inte finns några kategorier sen innan
    if (!await db.Categories.AnyAsync())
    {
        db.Categories.AddRange(
            new Category { CategoryName = "Books", CategoryDescription = "All books we have!" },
            new Category { CategoryName = "Movies", CategoryDescription = "All movies we have!" }
            );
        await db.SaveChangesAsync();
        Console.WriteLine("Seeded db!");
    }

    if (!await db.Products.AnyAsync())
    {
        db.Products.AddRange(
            new Product { ProductName = "Hammare", ProductDescription = "Slå en spik", ProductPrice = 249, },
            new Product { ProductName = "Tröja", ProductDescription = "Alla tröjor vi har", ProductPrice = 130 }
            );
        await db.SaveChangesAsync();
        Console.WriteLine("Seeded db with products!");
    }

}

// CLI för CRUD; CREATE, READ, UPDATE, DELETE
while (true)
{
    Console.WriteLine("\n Commands: addproduct | productlist | categorylist | add | delete <id> | edit <id> | exit");
    Console.WriteLine("> ");
    var line = Console.ReadLine()?.Trim() ?? string.Empty;

    // hoppa över tomma rader
    if (string.IsNullOrEmpty(line))
    {
        continue;
    }
     
    if (line.Equals("exit", StringComparison.OrdinalIgnoreCase))
    {
        break; // Avsluta programmet, hoppa ur loopen
    }

    // Delar upp raden på mellanslag: t.ex "eddit 2" --> ["edit", "2"]
    var parts = line.Split(' ', StringSplitOptions.RemoveEmptyEntries);
    var cmd = parts[0].ToLowerInvariant();

    //Enkel switch för kommandotolkning
    switch (cmd)
    {
        case "addproduct":
            await AddProductAsync();
            break;

        case "productlist":
            await ProductListAsync();
            break;

        case "categorylist":
            // Lista vår categories
            await ListAsync();
            break;

        case "add":
            await AddAsync();
            break;

        case "edit":
            // Körver id efter kommandot "edit"
            if (parts.Length < 2 || !int.TryParse(parts[1], out var id))
            {
                Console.WriteLine("Usage: Edit <id>");
                break;
            }
            await EditAsync(id);
            break;

        case "delete":
            // Radera en category
            if (parts.Length < 2 || !int.TryParse(parts[1], out var idD))
            {
                Console.WriteLine("Usage: Delete <id>");
                break;
            }
            await DeleteAsync(idD);
            break;

        default:
            Console.Write("Unknown command: ");
            break;
    }
}

static async Task DeleteAsync(int id)
{
    using var db = new ShopContext();

    // Hämta raden du vill ta bort igen
    var category = await db.Categories.FirstOrDefaultAsync((c => c.CategoryId == id));

    if (category == null)
    {
        Console.WriteLine("Category not found!");
        return;
    }

    db.Categories.Remove(category);

    try
    {
        await db.SaveChangesAsync();
        Console.WriteLine("Category deleted!");
    }
    catch (DbUpdateException exception)
    {
        Console.WriteLine(exception.Message);
    }
}

static async Task EditAsync(int id)
{
    using var db = new ShopContext();

    // Hämta radre vi vill uppdatera
    var category = await db.Categories.FirstOrDefaultAsync((x => x.CategoryId == id));

    if (category == null)
    {
        Console.WriteLine("Category not found");
        return;
    }

    // Visar nuvarande värden; Uppdatera namn för en specifik category
    Console.Write($"{category.CategoryName} ");
    var name = Console.ReadLine()?.Trim() ?? string.Empty;

    if (!string.IsNullOrEmpty(name))
    {
        category.CategoryName = name;
    }

    // Uppdatera description för en specifik category
    Console.Write($"{category.CategoryDescription} ");
    var description = Console.ReadLine()?.Trim() ?? string.Empty;

    if (!string.IsNullOrEmpty(description))
    {
        category.CategoryDescription = description;
    }

    // Uppdatera
    try
    {
        await db.SaveChangesAsync();
        Console.WriteLine("Edited!");
    }
    catch (DbUpdateException exception)
    {
        Console.WriteLine(exception.Message);
    }
}

// READ: lista alla categories
static async Task ListAsync()
{
    // Ny context per separation (bra praxis)
    using var db = new ShopContext();

    // AsNoTracking = snabbare får read-only scenarion (Ingen change tracking)
    var rows = await db.Categories.AsNoTracking().OrderBy(category => category.CategoryId).ToListAsync();
    Console.WriteLine("Id | Name | Description");

    foreach (var row in rows)
    {
        Console.WriteLine($"{row.CategoryId} | {row.CategoryName} | {row.CategoryDescription}");
    }
}

// Lists all products
static async Task ProductListAsync()
{
    // Ny context per separation (bra praxis)
    using var db = new ShopContext();

    // AsNoTracking = snabbare får read-only scenarion (Ingen change tracking)
    var productRow = await db.Products.AsNoTracking().OrderBy(p => p.ProductId).ToListAsync();
    Console.WriteLine("ProductId | Product Name | Product Description");

    foreach (var row in productRow)
    {
        Console.WriteLine($"{row.ProductId} | {row.ProductName} | {row.ProductDescription}");
    }
}

// CREATE: lägg till en ny category
static async Task AddAsync()
{
    Console.WriteLine("Name: ");
    var name = Console.ReadLine()?.Trim() ?? string.Empty;

    // Enkel validering
    if (string.IsNullOrEmpty(name) || name.Length > 100)
    {
        Console.WriteLine("Name is required (max 100).");
        return;
    }

    Console.WriteLine("Descriptions. (Optional): ");
    var desc = Console.ReadLine()?.Trim() ?? string.Empty;

    using var db = new ShopContext();
    db.Categories.Add(new Category { CategoryName = name, CategoryDescription = desc });

    try
    {
        // Spara våra ändringar
        await db.SaveChangesAsync();
        Console.WriteLine("Category added!");
    }
    catch (DbUpdateException exception)
    {
        Console.WriteLine("DB Error (Maybe duplicate?)! " + exception.GetBaseException().Message);
    }
}

static async Task AddProductAsync()
{
    Console.WriteLine("Product Name: ");
    var name = Console.ReadLine()?.Trim() ?? string.Empty;

    // Enkel validering
    if (string.IsNullOrEmpty(name) || name.Length > 100)
    {
        Console.WriteLine("Name is required (max 100 characters).");
        return;
    }

    Console.Write("Price ");
    var stringPrice = Console.ReadLine()?.Trim() ?? string.Empty;

    if (string.IsNullOrEmpty(stringPrice) || !decimal.TryParse(stringPrice, out var price) || price < 0)
    {
        Console.WriteLine("Price cannot be empty. Has to be a number!");
        return;
    }

    Console.WriteLine("Descriptions. (Optional): ");
    var desc = Console.ReadLine();

    using var db = new ShopContext();
    db.Products.Add(new Product { ProductName = name, ProductPrice = price, ProductDescription = desc, });

    try
    {
        // Spara våra ändringar
        await db.SaveChangesAsync();
        Console.WriteLine("Product added!");
    }
    catch (DbUpdateException exception)
    {
        Console.WriteLine("DB Error (Maybe duplicate?)! " + exception.GetBaseException().Message);
    }
}

Here is my other code in case they are related to the problem.

My ShopContext.cs:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace EF_Core___Migrations___Seeding
{
    // DbContext = "Enheten" som representerar databasen
    public class ShopContext : DbContext
    {
        // DB<Category> mappar till tabellen "Category" i databasen
        public DbSet<Category> Categories => Set<Category>();

        public DbSet<Product> Products => Set<Product>();

        // Här berättar vi för EF Core att vi vill använda SQLite och var filen ska finnas
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var dbPath = Path.Combine(AppContext.BaseDirectory, "shop.db");

            optionsBuilder.UseSqlite($"Filename={dbPath}");
        }

        // OnModelCreating används för att finjustera modellen
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Category>(e =>
            {
                // Sätter primärnyckel
                e.HasKey(x => x.CategoryId);

                // Säkerställer samma regler som data anotations (required + MaxLenght)
                e.Property(x => x.CategoryName)
                    .IsRequired().HasMaxLength(100);

                e.Property(x => x.CategoryDescription).HasMaxLength(250);

                // Skapar ett UNIQUE-index på CategoryName
                // Databasen tillåter inte två rader med samma CategoryName
                // Kanske inte vill ha två kategorier som heter "Books"
                e.HasIndex(x => x.CategoryName).IsUnique();
            });

            modelBuilder.Entity<Product>(e =>
            {
                // Sätter primärnyckel (PK)
                e.HasKey(x => x.ProductId);

                // Säkerställer samma regler som data annotations (required + MaxLenght)
                e.Property(x => x.ProductName)
                    .IsRequired().HasMaxLength(100);

                e.Property(x => x.ProductPrice).IsRequired();

                e.Property(x => x.ProductDescription).HasMaxLength(250);

                // TODO: UNCOMMENT
                //  e.HasIndex(x => x.ProductName).IsUnique();

                e.HasOne(p => p.Category)
                .WithMany(c => c.Products)
                .HasForeignKey(p => p.CategoryId)
                .OnDelete(DeleteBehavior.Restrict);
            });
        }
    }
}

Product.cs:

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EF_Core___Migrations___Seeding
{
    public class Product
    {
        // Primär nyckeln
        public int ProductId { get; set; }

        [MaxLength(250)]
        public string? ProductDescription { get; set; }

        [Required, MaxLength(100)]
        public string ProductName { get; set; } = null!;

        [Required]

        public decimal ProductPrice { get; set; }
        public int CategoryId { get; set; }
        public Category Category { get; set; } = null!;
    }
}

And lastly my Category.cs:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EF_Core___Migrations___Seeding
{
    //Enkel modellklass (entity) som EF Core mappa till en tabell "Category"
    public class Category
    {
        // Primärnycke. EF Core ser "CategoryId" och gör det till PK
        public int CategoryId { get; set; }

        // Required = får inte vara null (varken i C# eller i databasen)
        // MaxLength = genererar en kolumn med maxlängd 100 + används vid validering
        [Required, MaxLength(100)]
        public string CategoryName { get; set; } = null!;

        // Optional (Nullable '?') text med max 250 tecken
        [MaxLength(250)]
        public string? CategoryDescription { get; set; }

        public ICollection<Product> Products { get; set; } = new List<Product>();
    }
}

I ran your code and the exception was occured on await db.SaveChangesAsync();, not during migration. Is your database contains some data before? — CDnX
– CDnX, Commented Nov 26 at 2:14
@CDnX I don't know, database containing data might be the case but I don't know how to resolve that if that is what the problem stems from. — Hockaj
– Hockaj, Commented Nov 26 at 13:18
@CDnX I managed to delete the data that caused me to have the migration error thanks to you pointing it out that it was due to the database containing data beforehand. But now the error I get is the same you're getting, being await db.SaveChangesAsync(); — Hockaj
– Hockaj, Commented Nov 26 at 13:42

CDnX · Accepted Answer · 2025-11-26 02:00:44Z

1

It's because you set Product.CategoryId not nullable and tried to insert Product entity with no Category specified.

If you want it to be nullable, change your Product.cs like this and migrate.

namespace EF_Core___Migrations___Seeding
{
    public class Product
    {
        // ...
        public int? CategoryId { get; set; }
        // ...
    }
}

Or, if you don't want it to be nullable, you should put Category on your new Product.

// Program.cs

using (var db = new ShopContext())
{
    // ...

    if (!await db.Products.AnyAsync())
    {
        Category category = db.Categories.Where(c => c.CategoryName == "Books").Single();

        db.Products.AddRange(
            new Product { ProductName = "Hammare", ProductDescription = "Slå en spik", ProductPrice = 249, Category = category },
            new Product { ProductName = "Tröja", ProductDescription = "Alla tröjor vi har", ProductPrice = 130, Category = category }
            );
        await db.SaveChangesAsync();
        Console.WriteLine("Seeded db with products!");
    }
}

answered Nov 26 at 2:00

CDnX

6354 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vaishali Dhokchawle 2 days ago

Did this resolved your issue? As earlier issue is already solved of foreign key constraint.

Collectives™ on Stack Overflow

'SQLite Error 19: 'FOREIGN KEY constraint failed'.' while try to insert data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related