mysql - SQLSTATE[23000] : Integrity constraint violation: 1452 after adding two fields to table

Question

几个月来我一直在使用这个数据库。今天我向历史表 (attend, attend_date) 添加了两个字段，我的 MySQL 数据库崩溃了。

下面是两个相关的表:

CREATE TABLE `attendees` (
`attendeeid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`fname` varchar(20) NOT NULL,
`lname` varchar(20) NOT NULL,
`dojid` varchar(10) NOT NULL,
`address1` varchar(25) NOT NULL,
`address2` varchar(25) NOT NULL,
`city` varchar(20) NOT NULL,
`state` char(2) NOT NULL,
`zipcode` varchar(5) NOT NULL,
`phonenumber` varchar(15) NOT NULL,
`paroleofficer` varchar(20) NOT NULL,
`childrest` char(1) NOT NULL,
`groupid` char(2) NOT NULL,
`casedetails` varchar(255) NOT NULL,
`disdate` date NOT NULL,
`memberid` int(10) unsigned NOT NULL,
PRIMARY KEY (`attendeeid`),
UNIQUE KEY `dojid` (`dojid`),
KEY `memberid` (`memberid`),
CONSTRAINT `attendees_ibfk_10` FOREIGN KEY (`memberid`) REFERENCES `members` (`memberid`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `history` (
`historyid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`amount` float NOT NULL,
`subsidy` char(1) NOT NULL,
`last_payment` date NOT NULL,
`amount_paid` float NOT NULL,
`balance` float NOT NULL,
`attend` char(1) NOT NULL DEFAULT 'N',
`attend_date` date NOT NULL,
`attendeeid` int(10) unsigned NOT NULL,
`memberid` int(10) unsigned NOT NULL,
 PRIMARY KEY (`historyid`),
 KEY `attendeeid` (`attendeeid`),
 CONSTRAINT `history_ibfk_10` FOREIGN KEY (`attendeeid`) REFERENCES `attendees` (`attendeeid`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我的插入语句:

$stmt = $db->prepare('INSERT INTO history(amount, subsidy, last_payment, amount_paid, balance, attend, attend_date, attendeeid, memberid)
     VALUES(:amt, :sub, CURDATE(), :amt_paid, :newbal, :attend, CURDATE(), :attid, :memid)');

$stmt->bindParam(':amt', $amount, PDO::PARAM_STR);
$stmt->bindParam(':sub', $subsidy, PDO::PARAM_STR);     
$stmt->bindParam(':amt_paid', $payment, PDO::PARAM_STR);
$stmt->bindParam(':newbal', $newbal, PDO::PARAM_STR); 
$stmt->bindParam(':attend', $attend, PDO::PARAM_STR);
$stmt->bindParam(':attid', $attid, PDO::PARAM_STR);
$stmt->bindParam(':memid', $memid, PDO::PARAM_STR);
$stmt->execute();

当该语句被执行时，我得到:

    exception 'PDOException' with message 'SQLSTATE[23000]: 
Integrity constraint violation: 
  1452 Cannot add or update a child row: 
   a foreign key constraint fails (
    `master`.`history`, CONSTRAINT `history_ibfk_13` FOREIGN KEY 
    (`attendeeid`) REFERENCES `attendees` (`attendeeid`) ON DELETE CASCADE)' 
    in /var/www/html/dfwit/wspostpayments.php:358 Stack trace: 
     #0 /var/www/html/dfwit/wspostpayments.php(358): 
      PDOStatement->execute() #1 {main}

我完全不知道为什么会收到此错误。我搜索过 SO 并发现了类似的错误，但没有一个可以帮助我理清现在损坏的数据库。有没有人看到什么是错的？

Answer 1

这意味着列的值 attendeeid在 table 上 history你插入的表不存在 attendees .因为你定义了这样一个约束——它应该

列的值 attendeeid在 table 上 history取决于 attendeeid 的值在 table 上 attendees

换句话说，你不能保存 history记录而不添加其 attendeeid先到父表 ( attendees)。

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html