CODEWITHSUNDEEP
mysqlstored-proceduresforeign-keysinnodb
ppetrov
ppetrov

Reputation: 3105

MySQL foreign key constraint error on insert in transaction

I have this error when inserting values into an association table during transaction:

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)

Here is the part of the schema that describes the tables used:

enter image description here

and here is the create statement of the genre table:

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

To resume, the Genre tables references EntityType and Style, that's all.

The error occurs when I try to create a new Style and then add an association in the Genre table.

Everything is within a transaction, and what I do is:

I've searched quite a while on the web, but the only thing I found was this SO post: In MySQL, can I defer referential integrity checks until commit

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 (EntityType). Or am I missing something?

Can someone explain me the reason why I have this error please? (I'm stuck here)

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?

Thanks for your answers

EDIT

the first query to insert a new style is:

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

the next one, that produces the error:

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

both are actually stored procedures that we call from C# using MySQL.Net connector

the p_idEntityType comes from the model (MVC) and I checked it's value is correct and exists in EntityType table.

Upvotes: 0

Views: 3106

Answers (1)

Federico Razzoli
Federico Razzoli

Reputation: 5391

The error message is clear: entitytype is referenced by genre. This means that ALL rows in entitytype must have a match in genre, or can't be inserted. There is a match when genre.entitytype = entitytype.entitytype.

Upvotes: 1

Related Questions