topic Violation of PRIMARY KEY in tDBUpdate in MS SQL where duplicates not exists in Talend Studio

Violation of PRIMARY KEY in tDBUpdate in MS SQL where duplicates not exists

EspenH — Tue, 01 Apr 2025 11:36:56 GMT

I have a subjob where i use a tMap to join two tables and where the output is connected to a tDBOutput. When I run the job I get an error on multiple lines on the mentioned table:

Violation of PRIMARY KEY constraint 'PK__Gods__FD36D7E647FE53B6'. Cannot insert duplicate key in object 'edw.Gods'. The duplicate key value is (333715, 1).

[ERROR] 13:12:01 tso.initialload_all_tables_hogia_sql_server_0_1.InitialLoad_all_tables_Hogia_SQL_Server- tDBOutput_14 - Violation of PRIMARY KEY constraint 'PK__Gods__FD36D7E647FE53B6'. Cannot insert duplicate key in object 'edw.Gods'. The duplicate key value is (333715, 1).

On the tDBOutput the action on the table is "dropp table if exists and create" and action on data is "Insert". In the schema I have specified two columns as key. They are both key columns in the original source which also is a MS SQL data base.

I have verified than the transactions where I get an error using both the primary keys are in fact unique in the source.

In the tDBOutput job I have referenced the data source by "use an existing connection". This is connected in a tPrejob and tDBConnection wit "Auto commit".

Do any of you Talend experts know why I get this error?

Re: Violation of PRIMARY KEY in tDBUpdate in MS SQL where duplicates not exists

quentin-vigne — Tue, 01 Apr 2025 11:45:22 GMT

Hi @EspenH

Can you show us the setup of your tMap ?

Also maybe try to replace the tDBOutput with a tFileOutputDelimited and try to see if there is any duplicate key in the file. Maybe you are generating duplicates by doing the join in the tMap component.

- Quentin

Re: Violation of PRIMARY KEY in tDBUpdate in MS SQL where duplicates not exists

EspenH — Tue, 01 Apr 2025 12:26:21 GMT

Hi Quentin

Thank you for replying!

And thanks to your reply i started to go throug my setup. The input was not the straight source table, but another tMap where i created duplicates of the key. Arrrrrhhh...stupid...

Thank you!!!