MySQL:插入和更新时出错:无法添加或更新子行:外键约束失败 - MySQL: Error when insert and update: Cannot add or update a child row: a foreign key constraint fails

MySQL: Error when insert and update: Cannot add or update a child row: a foreign key constraint fails

我一直在阅读一些与我有相同问题的问题，但还没有找到解决方案

数据库结构

CREATE TABLE material (
  cve_mat varchar(12),
  type_mat varchar(20),
  author_mat varchar(40),
  status varchar(2),
  primary key(cve_mat)   
) ENGINE = INNODB;
CREATE TABLE users (
  cve_user varchar(8),
  name_user varchar(25),
  lastn_user varchar(25),
  email_user varchar(25),
  primary key(cve_user)
)ENGINE = INNODB;
CREATE TABLE material_user (
  cve_mat varchar(8),
  cve_user varchar(8),
  start_mat date,
  end_mat date,
  foreign key(cve_mat) references material(cve_mat)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  foreign key(cve_user) references users(cve_user)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE = INNODB;

当执行这些指令时：

......
$query = "INSERT INTO material_user VALUES ('$cvemat','$ncontrol',NOW(),DATE_ADD(current_date, interval '$days' day))";
$result = mysql_query($query) or die(mysql_error());
if ($result) {
$query = "UPDATE material SET status = 'YES' WHERE material.cve_mat = '$cvemat'";
$result = mysql_query($query) or die(mysql_error());
if ($result){
    return true;
} else {
    return false;
}
} else {
return false;
}

并显示此错误：

Cannot add or update a child row: a foreign key constraint fails (`biblioteca/material_prestamo`, CONSTRAINT `material_prestamo_ibfk_1` FOREIGN KEY (`cve_mat`) REFERENCES `material` (`cve_mat`) ON DELETE CASCADE ON UPDATE CASCADE)

我将非常感谢您的帮助！

首先，外键的列类型应与引用表匹配：

material_user

cve_mat varchar(8),

材料

cve_mat varchar(12),

这两种列类型应相等。

在插入语句之前，您可以执行一些调试：

SELECT * FROM material WHERE cve_mat='$cvemat'

如果一切正常，您可以运行以下命令以查找更多详细信息：

SHOW INNODB STATUS;

您有外键约束，因此您需要先执行第二个查询，然后再执行第一个查询。它可能会帮助你

$query = "UPDATE material SET status = 'YES' WHERE material.cve_mat = '$cvemat'";
$result = mysql_query($query) or die(mysql_error());
if ($result) {
   $query = "INSERT INTO material_user VALUES  ('$cvemat','$ncontrol',NOW(),DATE_ADD(current_date, interval '$days' day))";
   $result = mysql_query($query) or die(mysql_error());