php - 外键约束在事务中失败

Question

我正在使用 PHP/MySQL + Yii Framework 开发一个基于网络的应用程序。该问题作为事务中的约束检查错误出现。

我有以下表格:

用户

CREATE TABLE IF NOT EXISTS `User` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `surname` varchar(64) DEFAULT NULL,
  `email` varchar(128) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `creation_date` datetime DEFAULT NULL,
  `last_login_date` datetime DEFAULT NULL,
  `status` tinyint(1) DEFAULT '0',
  `level` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=40 ;

候选人信息

CREATE TABLE IF NOT EXISTS `CandidateInfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `candidate_status_id` int(11) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL,
  `surname` varchar(64) DEFAULT NULL,
  `email` varchar(128) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT '0',
  `date_of_birth` datetime DEFAULT NULL,
  `home_phone` varchar(20) DEFAULT NULL,
  `mobile_phone` varchar(20) DEFAULT NULL,
  `creation_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `rating` tinyint(1) DEFAULT '0',
  `location` varchar(100)DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_candidateinfo_user` (`id`),
  KEY `FK_candidateinfo_candidatestatus` (`candidate_status_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=26 ;

基本上，我正在尝试向User 表添加一个新行，然后使用插入 ID 向 CandidateInfo 表(user_id 列)添加一个新行

php代码如下

$transaction = Yii::app()->db->beginTransaction();
try {
    $user->save();
    $candidate->setAttribute('user_id', $user->id);
    $candidate->save();
    $transaction->commit();
} catch (Exception $e) {
    $transaction->rollBack();
    var_dump($e->getMessage());
}

错误是:

 SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`origo`.`CandidateInfo`, CONSTRAINT `FK_candidateinfo_user` FOREIGN KEY (`id`) REFERENCES `User` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION). The SQL statement executed was: INSERT INTO `CandidateInfo` (`gender`, `rating`, `name`, `surname`, `email`, `date_of_birth`, `home_phone`, `mobile_phone`, `user_id`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6, :yp7, :yp8)

当我检查 mysql 查询日志时，我发现它为 CandidateInfo 表的 INSERT 语句采用了正确的 user_id。但因上述错误而失败。根据我的理解，它应该可以工作，但可能是我弄错了，这不是交易的工作方式。

两个表都是 InnoDB。

提前致谢。

编辑:抱歉忘了贴 FK 关系。

ALTER TABLE `CandidateInfo`
  ADD CONSTRAINT `FK_candidateinfo_candidatestatus` FOREIGN KEY (`candidate_status_id`) REFERENCES `CandidateStatus` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `FK_candidateinfo_user` FOREIGN KEY (`id`) REFERENCES `User` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Answer 1

ALTER TABLE `CandidateInfo`
  ADD CONSTRAINT `FK_candidateinfo_candidatestatus` FOREIGN KEY (`candidate_status_id`) REFERENCES `CandidateStatus` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `FK_candidateinfo_user` FOREIGN KEY (`id`) REFERENCES `User` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

应该是

ALTER TABLE `CandidateInfo`
  ADD CONSTRAINT `FK_candidateinfo_candidatestatus` FOREIGN KEY (`candidate_status_id`) REFERENCES `CandidateStatus` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `FK_candidateinfo_user` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

您的 ID 中有 id 引用 id。