SQLite添加产品时Foreign Key Constraint Failed问题求助

外键约束失败的核心原因是：你尝试插入的Seller_ID在Sellers表中不存在，或者SQLite的外键约束功能未启用，以下是具体排查和解决步骤：

1. 启用SQLite外键约束 #

SQLite默认关闭外键约束检查，即使表结构中定义了外键也不会生效。需要在每次打开数据库连接后手动启用：

在InitializeDB_PRODUCTDETAILS和AddProduct方法的con.Open()之后，添加以下代码：

SqliteCommand enableFKCmd = new SqliteCommand("PRAGMA foreign_keys = ON;", con);
enableFKCmd.ExecuteNonQuery();

2. 验证传入的seller_id有效性 #

调用AddProduct时传入的seller_id必须是Sellers表中已存在的主键值。可以在插入前增加验证逻辑，提前发现无效ID：

在AddProduct的con.Open()之后，加入卖家ID检查：

// 检查卖家ID是否存在
string checkSellerSql = "SELECT COUNT(*) FROM Sellers WHERE SELLER_ID = @Seller_ID";
using (SqliteCommand checkCmd = new SqliteCommand(checkSellerSql, con))
{
    checkCmd.Parameters.AddWithValue("@Seller_ID", seller_id);
    long count = (long)checkCmd.ExecuteScalar();
    if (count == 0)
    {
        // 这里可以抛出异常或返回错误提示给UI
        throw new Exception("该卖家ID不存在，请确认后重试");
    }
}

3. 确保表初始化顺序正确 #

必须先创建Sellers表，再创建ProductDetails表。否则ProductDetails表创建时，Sellers表还不存在，外键关联会失效。确保代码中先调用InitializeDB_SELLERACCOUNTS()，再调用InitializeDB_PRODUCTDETAILS()。

修改后的完整AddProduct示例 #

public static void AddProduct(int seller_id, string productname, string productcategory, double productprice, string productdescription, int productquantity, byte[] productpicture)
{
    string pathtoDB = Path.Combine(ApplicationData.Current.LocalFolder.Path, "MyDatabase.db");

    using (SqliteConnection con = new SqliteConnection($"Filename={pathtoDB}"))
    {
        con.Open();
        // 启用外键约束
        SqliteCommand enableFKCmd = new SqliteCommand("PRAGMA foreign_keys = ON;", con);
        enableFKCmd.ExecuteNonQuery();

        // 验证卖家ID存在性
        string checkSellerSql = "SELECT COUNT(*) FROM Sellers WHERE SELLER_ID = @Seller_ID";
        using (SqliteCommand checkCmd = new SqliteCommand(checkSellerSql, con))
        {
            checkCmd.Parameters.AddWithValue("@Seller_ID", seller_id);
            long count = (long)checkCmd.ExecuteScalar();
            if (count == 0)
            {
                throw new Exception("指定的卖家ID不存在");
            }
        }

        // 生成唯一SKU逻辑不变
        Random random = new Random();
        long productSKU;
        bool isUniqueSKU = false;
        do
        {
            productSKU = (long)(random.NextDouble() * (999999999999L - 100000000000L) + 100000000000L);
            string checkSKUQuery = "SELECT COUNT(*) FROM ProductDetails WHERE ProductSKU = @ProductSKU";
            using (SqliteCommand cmdCheckSKU = new SqliteCommand(checkSKUQuery, con))
            {
                cmdCheckSKU.Parameters.AddWithValue("@ProductSKU", productSKU);
                long existingCount = (long)cmdCheckSKU.ExecuteScalar();
                isUniqueSKU = existingCount == 0;
            }
        } while (!isUniqueSKU);

        // 插入产品逻辑不变
        string insertCMD = @"INSERT INTO ProductDetails (Seller_ID, ProductSKU, ProductName, ProductCategory, ProductPrice, ProductDescription, ProductQuantity, ProductPicture) 
                     VALUES (@Seller_ID, @ProductSKU, @ProductName, @ProductCategory, @ProductPrice, @ProductDescription, @ProductQuantity, @ProductPicture)";
        SqliteCommand cmdInsertRecord = new SqliteCommand(insertCMD, con);
        cmdInsertRecord.Parameters.AddWithValue("@Seller_ID", seller_id);
        cmdInsertRecord.Parameters.AddWithValue("@ProductName", productname);
        cmdInsertRecord.Parameters.AddWithValue("@ProductCategory", productcategory);
        cmdInsertRecord.Parameters.AddWithValue("@ProductPrice", productprice);
        cmdInsertRecord.Parameters.AddWithValue("@ProductDescription", productdescription);
        cmdInsertRecord.Parameters.AddWithValue("@ProductQuantity", productquantity);
        cmdInsertRecord.Parameters.AddWithValue("@ProductPicture", productpicture);
        cmdInsertRecord.Parameters.AddWithValue("@ProductSKU", productSKU);

        cmdInsertRecord.ExecuteNonQuery();
        con.Close();
    }
}

内容的提问来源于stack exchange，提问作者SirLKFD