Cannot add or update a child row: a foreign key constraint fails

[Anonymous]

Cannot add or update a child row: a foreign key constraint fails (`matriculas`.`gestaomatriculas`, CONSTRAINT `fk_GestaoMatriculas_Calendario1` FOREIGN KEY (`ID_Calendario_Ano_Fim`) REFERENCES `calendario` (`ID_Calendario`) ON DELETE NO ACTION ON UPDATE NO A)

I want to load my facts table but it gives me this error i dont know why, can someone help me please ?

This is my facts table:

CREATE TABLE IF NOT EXISTS `matriculas`.`gestaomatriculas` (
`ID_Localizacao` int(11) NOT NULL,
`ID_Calendario_Ano_Inicio` int(11) NOT NULL,
`ID_Calendario_Ano_Fim` int(11) NOT NULL,
`ID_Escola` int(11) NOT NULL,
`ID_Entidade` int(11) NOT NULL,
`ID_Curso` int(11) NOT NULL,
`numero_homens` int(11) DEFAULT NULL,
`numero_mulheres` int(11) DEFAULT NULL,
INDEX `fk_GestaoMatriculas_Localizacao1_idx` (`ID_Localizacao` ASC),
INDEX `fk_GestaoMatriculas_Calendario1_idx` (`ID_Calendario_Ano_Fim` ASC),
INDEX `fk_GestaoMatriculas_Escola1_idx` (`ID_Escola` ASC),
INDEX `fk_GestaoMatriculas_Entidade1_idx` (`ID_Entidade` ASC),
INDEX `fk_GestaoMatriculas_Oferta1_idx` (`ID_Curso`ASC),
INDEX `fk_GestaoMatriculas_Calendario2_idx` (`ID_Calendario_Ano_Inicio`ASC),
CONSTRAINT `fk_GestaoMatriculas_Calendario1`
FOREIGN KEY (`ID_Calendario_Ano_Fim`)
REFERENCES `matriculas`.`calendario` (`ID_Calendario`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_GestaoMatriculas_Calendario2`
FOREIGN KEY (`ID_Calendario_Ano_Inicio`)
REFERENCES `matriculas`.`calendario` (`ID_Calendario`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_GestaoMatriculas_Entidade1`
FOREIGN KEY (`ID_Entidade`)
REFERENCES `matriculas`.`entidade` (`ID_Entidade`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_GestaoMatriculas_Escola1`
FOREIGN KEY (`ID_Escola`)
REFERENCES `matriculas`.`escola` (`ID_Escola`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_GestaoMatriculas_Localizacao1`
FOREIGN KEY (`ID_Localizacao`)
REFERENCES `matriculas`.`localizacao` (`ID_Localizacao`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_GestaoMatriculas_Oferta1`
FOREIGN KEY (`ID_Curso`)
REFERENCES `matriculas`.`curso` (`ID_Curso`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ENGINE=InnoDB

This is the dimension "calendario" :

CREATE TABLE IF NOT EXISTS `Matriculas`.`calendario` (
`ID_Calendario` int(11) NOT NULL AUTO_INCREMENT,
`ano` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID_Calendario`)
) ENGINE=InnoDB

[Anonymous]

Hi,

   Since you have Foreign Key constraint, you must first load all the dimension tables first before loading the fact table. If the error persists, could you please share a screen shot of the job so that we will get better clarity of the job flow?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[TRF]

As soon as you try to insert a row into the fact table, the value in the field ID_Calendario_Ano_Inicio must identify a row in the table `matriculas`.`calendario` with the same value for the field ID_Calendario

[Anonymous]

I have changed the ID_Calendario_Ano_Inicio to just ID_Calendario and its working fine, but now i have the same error for other ID's, like :
"Cannot add or update a child row: a foreign key constraint fails (`matriculas`.`gestaomatriculas`, CONSTRAINT `fk_GestaoMatriculas_Escola1` FOREIGN KEY (`ID_Escola`) REFERENCES `escola` (`ID_Escola`) ON DELETE NO ACTION ON UPDATE NO ACTION)"

I have loaded all the dimensions for my facts table, and all have the ID's, exact same name as other ID's.

MySQL for dimension "escola"facts tablefacts table

[Anonymous]

Error

[Anonymous]

Hi,

    Its not about changing the foreign key id. You will have to make sure that all the dimension tables are filled first.

    Whenever an input file is coming from source below are the high level steps.

a) Load the data to all dimension tables and generate the corresponding dimension id. 

b) read the input file again and do inner join for all the columns with corresponding dimension tables (where you have foreign key constraints) to get dimension ids

c) All the records which have dimension ids can be passed to target table

d) All the rejected records in tMap can be transferred to a reject table or file for further analysis.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[Anonymous]

I changed the Join Model to "Inner join" and now he doesnt pass the rows

[Anonymous]

merge dimensions, facts tables

[Anonymous]

Hi,

    It means dimension data is not correctly recorded in the dimension tables. Did you query the table and see whether the data is present there?

    Now, in tMap, you can add one more output column where you can capture the reject records (inner join reject). This data also will give some information about possible problem. Also please check the underlying data whether it is having extra spaces or the case of the string is different. 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[Anonymous]

It is, all the dimensions have loaded to the database dimension escoladimension entidadedimension curso