Foreign key constraint fails in transaction

Question

I'm developing a web-based application with PHP/MySQL + Yii Framework. The problem occurs as a constraint check error in a transaction.

I have the following tables:

User

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 ;

CandidateInfo

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 ;

Basically I'm trying to add a new row to User table, and then use the insert id to add a new row to the CandidateInfo table (user_id column)

The php code is as

$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());
}

The error is:

 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)

When i check the mysql query logs, i see that it takes the right user_id for the INSERT statement for CandidateInfo table. But fails with the above error. From my understanding, it is supposed to work, but may be i am mistaken and this is not the way transactions are meant to work.

Both tables are InnoDB.

Thanks in advance.

Edit: Sorry forgot to paste the FK relations.

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;

Solution 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;

should be

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;

you've got id references id in yours.

Solution 2

Your error stack:

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 )

Your CandidateInfo table defines id field as auto_increment primary key field and a foreign key as well.
And your insert statement does not include id, read from its parent user table.
And hence on insert a new id value is generated for candidateinfo table and applied.
Which intern failed as it did not match any of the primary key id value of the parent user table.

And hence is the error.

Note:
In a child table if your are referring a pk field of a master as a foreign key field,
you should not apply auto_increment for it but just refer.

---

And looking closely the candidateinfo structure, I feel that you might want to map use_id to user.id field. Making that change, with proper foreign key definition used, would resolve your problem.

Comments

[retri]

ya, my bad. Obviously I overlooked while writing the table definition. The user_id (not id) column is supposed to reference the id of User table.