title: Foreign key constraint fails in transaction
body: <p>I'm developing a web-based application with PHP/MySQL + Yii Framework. The problem occurs as a constraint check error in a transaction.</p>
  
  <p>I have the following tables:</p>
  
  <h2>User</h2>
  
  <pre><code>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 ;
  </code></pre>
  
  <h2>CandidateInfo</h2>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>Basically I'm trying to add a new row to <strong>User</strong> table, and then use the insert id to add a new row to the <strong>CandidateInfo</strong> table (user_id column)</p>
  
  <p>The php code is as </p>
  
  <pre><code>$transaction = Yii::app()-&gt;db-&gt;beginTransaction();
  try {
      $user-&gt;save();
      $candidate-&gt;setAttribute('user_id', $user-&gt;id);
      $candidate-&gt;save();
      $transaction-&gt;commit();
  } catch (Exception $e) {
      $transaction-&gt;rollBack();
      var_dump($e-&gt;getMessage());
  }
  </code></pre>
  
  <p>The error is:</p>
  
  <pre><code> 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)
  </code></pre>
  
  <p>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.</p>
  
  <p>Both tables are InnoDB.</p>
  
  <p>Thanks in advance.</p>
  
  <p><strong>Edit:</strong> 
  Sorry forgot to paste the FK relations.</p>
  
  <pre><code>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;
  </code></pre>