topic Re: Cannot add or update a child row: a foreign key constraint fails in Talend Studio

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

Anonymous — Sat, 16 Nov 2024 04:03:02 GMT

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

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

Anonymous — Fri, 22 Nov 2019 04:41:07 GMT

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

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

TRF — Fri, 22 Nov 2019 07:29:49 GMT

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

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

Anonymous — Fri, 22 Nov 2019 15:24:30 GMT

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

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

Anonymous — Fri, 22 Nov 2019 15:26:55 GMT

Error

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

Anonymous — Fri, 22 Nov 2019 15:46:31 GMT

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

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

Anonymous — Fri, 22 Nov 2019 16:24:39 GMT

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

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

Anonymous — Fri, 22 Nov 2019 16:26:53 GMT

merge dimensions, facts tables

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

Anonymous — Fri, 22 Nov 2019 17:45:26 GMT

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

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

Anonymous — Fri, 22 Nov 2019 18:32:29 GMT

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

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

Anonymous — Fri, 22 Nov 2019 22:11:49 GMT

Hi,

Please verify whether you are getting right values by adding tLogrow before tMap for each input data source. You may have to trim the data to avoid any data mismatch. Please also cross verify whether you are using UTF8 since the data seems to be not in English. This could identify the possible root cause.

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

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

Anonymous — Sat, 23 Nov 2019 13:57:19 GMT

Doesnt pass the ID's i cant understand why, help me please fact tabletFileOutputDelimited

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

Anonymous — Sun, 24 Nov 2019 04:12:48 GMT

Hi,

Could you please add tLogrow and see the data? Use 5 or 10 input records for main row and restrict the records in lookup also. Then you will be able to check the details easily.

If you are adding dimension data in same job, could you please double check whether the data for dimension tables have been committed before querying them again for lookup?

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