title: MySQL foreign key constraint error on insert in transaction
body: <p>I have this error when inserting values into an association table during transaction:</p>
  
  <p><code>Cannot add or update a child row: a foreign key constraint fails (dev.genre, CONSTRAINT fk_Genre_EntityType1 FOREIGN KEY (idEntityType) REFERENCES entitytype (idEntityType) ON DELETE NO ACTION ON UPDATE NO ACTION)</code></p>
  
  <p>Here is the part of the schema that describes the tables used:</p>
  
  <p><img src="https://i.stack.imgur.com/e4IIk.png" alt="enter image description here"></p>
  
  <p>and here is the create statement of the <code>genre</code> table:</p>
  
  <pre><code>-- -----------------------------------------------------
  -- Table `dev`.`Genre`
  -- -----------------------------------------------------
  
  CREATE  TABLE IF NOT EXISTS `dev`.`Genre` (
    `idGenre` INT NOT NULL ,
    `Name` VARCHAR(45) NULL COMMENT 'musique, spectacle, expo' ,
    `idEntityType` INT NOT NULL ,
    `idStyle` INT NOT NULL ,
    PRIMARY KEY (`idGenre`, `idEntityType`, `idStyle`) ,
    INDEX `fk_Genre_EntityType1_idx` (`idEntityType` ASC) ,
    INDEX `fk_Genre_Style1_idx` (`idStyle` ASC) ,
    CONSTRAINT `fk_Genre_EntityType1`
      FOREIGN KEY (`idEntityType` )
      REFERENCES `dev`.`EntityType` (`idEntityType` )
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_Genre_Style1`
      FOREIGN KEY (`idStyle` )
      REFERENCES `dev`.`Style` (`idStyle` )
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  </code></pre>
  
  <p>To resume, the <code>Genre</code> tables references <code>EntityType</code> and <code>Style</code>, that's all.</p>
  
  <p>The error occurs when I try to create a new <code>Style</code> and then add an association in the <code>Genre</code> table.</p>
  
  <p>Everything is within a transaction, and what I do is:  </p>
  
  <ul>
  <li>create the new style in the <code>Style</code> table</li>
  <li>get the id of the created style</li>
  <li>insert an association in the <code>genre</code> table, and that's when I get the error.</li>
  </ul>
  
  <p>I've searched quite a while on the web, but the only thing I found was this SO post: <a href="https://stackoverflow.com/questions/5014700/in-mysql-can-i-defer-referential-integrity-checks-until-commit">In MySQL, can I defer referential integrity checks until commit</a></p>
  
  <p>I'm not sure this is what it is about here, because the error happens on a table that hadn't changed during the transaction (<code>EntityType</code>). Or am I missing something?</p>
  
  <p>Can someone explain me the reason why I have this error please? (I'm stuck here)</p>
  
  <p>Also, if it really have something to do with the SO post I mentioned earlier, is there a "clean" way of doing that kind of inserts without writing my own rollback mechanism?</p>
  
  <p>Thanks for your answers</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>the first query to insert a new style is:</p>
  
  <pre><code>CREATE PROCEDURE `Entity_CreateStyle`
  (
      IN p_name varchar(45),
      OUT op_idStyle int(11)
  )
  BEGIN
      insert into Style(idParentStyle, Name, IsValidated)
      values(null, p_name, 0);
  
      SET op_idStyle = LAST_INSERT_ID();
  END
  </code></pre>
  
  <p>the next one, that produces the error:</p>
  
  <pre><code>CREATE PROCEDURE `Entity_AssociateStyleWithEntityType`
  (
      IN p_idGenre int(11),
      IN p_Name varchar(45),
      IN p_idEntityType int(11),
      IN p_idStyle int(11)
  )
  BEGIN
      insert into Genre(idGenre, Name, idEntityType, idStyle)
      values(p_idGenre, p_Name, p_idEntityType, p_idStyle);
  END
  </code></pre>
  
  <p>both are actually stored procedures that we call from C# using MySQL.Net connector</p>
  
  <p>the <code>p_idEntityType</code> comes from the model (MVC) and I checked it's value is correct and exists in <code>EntityType</code> table.</p>