topic Re: Fk (Constraint error) - "Cannot add or update a child row" in Talend Studio

Fk (Constraint error) - "Cannot add or update a child row"

Anonymous — Thu, 12 Apr 2018 15:22:13 GMT

Hi, everyone! Did anyone ever come across this problem? I'm trying to insert data into my dw.

I already disabled my FKs on my data warehouse. But it won't go anyways.

Here's the error:

Cannot add or update a child row: a foreign key constraint fails
(`dw_bi_web`.`fact_lancamento_contabil`, CONSTRAINT `fk_fact_lancamento_contabil_dim_conta_contabil1`
 FOREIGN KEY (`dim_conta_contabil_sk_conta_contabil`) 
REFERENCES `dim_conta_contabil` (`sk_c)
[statistics] disconnected
Job carga_fato_contas ended at 11:11 12/04/2018. [exit code=0]

Thanks!

Re: Fk (Constraint error) - "Cannot add or update a child row"

Anonymous — Fri, 04 May 2018 05:06:35 GMT

Hello,

Could you please clarify in which Talend version/edition you are?

Best regards

Sabrina

Re: Fk (Constraint error) - "Cannot add or update a child row"

manodwhb — Fri, 04 May 2018 05:30:37 GMT

@isadoralmeida06,this is DB related issue.

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table.

It will reject any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table.

Please check the the vaule which you were instering or updating in `dw_bi_web`.`fact_lancamento_contabil` are presnt in `dim_conta_contabil`.

Re: Fk (Constraint error) - "Cannot add or update a child row"

TRF — Fri, 04 May 2018 05:58:21 GMT

@isadoralmeida06, did you use drop contraint for this foreign key too?
@manodwhb, it's a good practice to give URL when copying part of text from elsewhere.

Re: Fk (Constraint error) - "Cannot add or update a child row"

TRF — Mon, 07 May 2018 14:14:31 GMT

@isadoralmeida06, did this help you?

If so, thank's to mark your case as solved (Kudos also accepted).

Re: Fk (Constraint error) - "Cannot add or update a child row"

Anonymous — Mon, 07 May 2018 14:14:49 GMT

@TRF and @manodwhb

Basically, yes it does have an fk in my child table, but the problem is, it should be working.
So I have a field on my parent table (item) from my data warehouse called "item_description" which is a long string, e.g : "1-The car was cleaned on time.".
And then on my database (origem), I have a table that have this same column with exact same rows "1-The car was cleaned on time." and then when I do mapping, it gives me the FK error, and when i dropped my FK constraint, it says that my sk is null, so my fact table has all NULLs on the item_sk.

Does anyone know how to make talend get it that if a text is equal to the other, get the correspondent SK?

All my other maps are working, but the matches are between, few words or number.

Re: Fk (Constraint error) - "Cannot add or update a child row"

Anonymous — Mon, 07 May 2018 14:19:48 GMT

Totally agree. But there are matches. All the values that I have, there's a specific match on my dimension table.

Re: Fk (Constraint error) - "Cannot add or update a child row"

TRF — Mon, 07 May 2018 14:20:04 GMT

Can you share your tables definition?

What if you try to do same from SQL, not from a Talend job?

Re: Fk (Constraint error) - "Cannot add or update a child row"

Anonymous — Mon, 07 May 2018 14:21:09 GMT

Like an inner join? I'll try!

Re: Fk (Constraint error) - "Cannot add or update a child row"

Anonymous — Mon, 07 May 2018 14:27:26 GMT

It works on my database. But all the project is on talend, cuz that's what my client wants. Anyways, I managed to work around it a couple of days ago,but I wish I could get it fixed by the BI rules, you know. Trought the Surrogate key. But I'll leave like this for now.

Thanks!

Re: Fk (Constraint error) - "Cannot add or update a child row"

TRF — Mon, 07 May 2018 14:30:40 GMT

Probably the field corresponding to the foreign key is empty or null when your job push the data flow to the DB.

Can you check this point?

Else can you share your job design with settings for the DB component and maybe any tMap before this component?

Re: Fk (Constraint error) - "Cannot add or update a child row"

Anonymous — Mon, 07 May 2018 14:33:58 GMT

No, there's no "Null" rows on my dimension, nor on my origem table. That's a "not null" column on my DBs.

Re: Fk (Constraint error) - "Cannot add or update a child row"

manodwhb — Mon, 07 May 2018 16:24:16 GMT

You need to set default value if the business keys are not between source and dimension when you populate data into fact table

Re: Fk (Constraint error) - "Cannot add or update a child row"

Anonymous — Mon, 07 May 2018 16:40:51 GMT

But there are matches, and even tough it's putting as null.

Re: Fk (Constraint error) - "Cannot add or update a child row"

manodwhb — Thu, 10 May 2018 14:12:10 GMT

@isadoralmeida06,your sure about the data then you might have configure wrongly that lookup.please check.

Re: Fk (Constraint error) - "Cannot add or update a child row"

Anonymous — Thu, 10 May 2018 15:16:31 GMT

Lookup is fine, all others are working in the same particular job, except this one.

I honestly think it's a talend issue, I don't think it can verify if long texts columns from differents sources match.

I fixed with a workaround...thanks anyways.

Re: Fk (Constraint error) - "Cannot add or update a child row"

manodwhb — Thu, 10 May 2018 16:51:41 GMT

How you fixed,do you used tjoin instead of tmap?

Re: Fk (Constraint error) - "Cannot add or update a child row"

Anonymous — Fri, 05 Oct 2018 12:46:43 GMT

It happens to me just like you. But I got the "[statistics] disconnected"

For what is this? and some solution