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

Six Stars

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

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!

Moderator

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

Hello,

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

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Thirteen Stars

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

@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`.

Manohar B
Forteen Stars TRF
Forteen Stars

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

@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.

TRF
Forteen Stars TRF
Forteen Stars

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

@isadoralmeida06, did this help you?

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


TRF
Six Stars

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

Hi

@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.

Six Stars

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

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

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

Can you share your tables definition?

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


TRF
Six Stars

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

Like an inner join? I'll try!
Six Stars

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

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!

Forteen Stars TRF
Forteen Stars

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

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?


TRF
Six Stars

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

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

Thirteen Stars

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

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

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

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

Thirteen Stars

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

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

Manohar B
Six Stars

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

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.

Thirteen Stars

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

How you fixed,do you used tjoin instead of tmap?
Manohar B
Four Stars

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

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

For what is this? and some solution