c# - 错误 - 无法添加或更新子行 : a foreign key constraint fails

Question

我有两张 table ，

purchase(purchId,date,empId,supId)
item(itemID,name,qty,unit_price)

并且，

purchase_item(purchId,itemID,qty,unit_price)

购买表和商品表更新得很好，但是当我尝试更新purchase_item时，

Cannot add or update a child row: a foreign key constraint fails (bookshop.purchase_item, CONSTRAINT purchase_item_ibfk_1 FOREIGN KEY (purchId) REFERENCES purchase (purchId) ON DELETE CASCADE ON UPDATE CASCADE)

使用更新语句，

Insert into purchase_item values('" + invoice + "','" + ItemId + "','" + qty + "','" + unitPrice + "')"; (working on a C# project)

代码，w

int ItemId = Convert.ToInt32(textBox3.Text);
        string ItemName = textBox4.Text;
        string Category = textBox5.Text;
        int qty = Convert.ToInt32(textBox7.Text);
        double UnitPrice = Convert.ToDouble(textBox6.Text);
        string supname = textBox8.Text;
        int supId = 12345;
        int invoice = Convert.ToInt32(textBox9.Text);
        string query1 = "Insert into item values('" + ItemId + "','" + ItemName + "','" + Category + "','" + qty + "','" + UnitPrice + "')";
        int rows = db.SaveUpdateDelete(query1);

        DateTime d = DateTime.Now;
        int day = d.Day;
        int month = d.Month;
        int year = d.Year;

        string dt = year + "/" + month + "/" + day;


        string query = "Insert into purchase values("+invoice+",'"+dt+"',"+username+","+supId+")";
        string query2 = "Insert into purchase_item values('" + invoice + "','" + ItemId + "','" + qty + "','" + UnitPrice + "')";

        int rows1 = db.SaveUpdateDelete(query);
        int rows2 = db.SaveUpdateDelete(query2);
        if  (rows==1 && rows1==1 && rows2==1)
        {


            MessageBox.Show("Data Entered Successfully.");
        }
        else
        {
            MessageBox.Show("Failed to Insert Data.");
        }           

Answer 1

您收到此错误的原因是，您尝试向purchase_item 添加一行，但根据当前存储在purchase 和item 表中的值，该行的itemID 或purchId 字段没有有效值。如果您再发布一些代码，我可以帮助您诊断具体原因。