Primary Key required for Update option, but Primary Key exists in Oracle

Hi,

I have a workflow which reads an API, parses JSON data and then needs to update / insert new records into an Oracle table. I have created the table using Alteryx and used the 'Post-Create SQL' to add a primary key (same SQL used in 'https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Create-Database-Table-Primary-Key-in-Alteryx/ta-p/18353'). Using SQL Developer / Alteryx's input tool, I was able to confirm that the primary key (with no-nullable constraints) was successfully created.

Primary Key identified in Alteryx.PNG

When I re-run the workflow, after modifying the Output tool (removing the Post-Create SQL, and changing the Output Options) to 'Update; insert if New', the tool always returns 'Primary Key required for Update option'... But the primary key definitely exists!

Workflow and Output Config.PNG

I read a support article from a senior Alteryx support officer who advised someone that the error can occur if the primary key data is not included within the data being loaded - but I can confirm that there are no null values in the data being loaded for the column being used for the primary key.

no nulls.png

Output

Accepted answers

dandev91

I had to remove the primary key and re-add it for it to work. I had to do this outside of Alteryx - I couldn't use the 'post' SQL to successfully create it. I used SQL Developer to add the constraint and primary key, and then commit the changes.

This worked, but now I face another issue I haven't seen before - which I will post another forum message for.

All comments

jrgo

Hi @dandev91,

Any reason why you had to customize your field mappings? If the table was created from your data, I would have assumed that there wouldn't be a need to manually map the fields. Maybe worth a check to make sure that the fields are mapped correctly or adjust it back to automatically map?

dandev91

The same error applies when left on the default 'By Field Name'. My apologies, I took the screenshot after I went through each and every setting and fiddled with things before giving up and posting here.

EmilyVA

I'm having the same problem - did you ever figure out what was going on / how to fix it?

@dandev91wrote:
The same error applies when left on the default 'By Field Name'. My apologies, I took the screenshot after I went through each and every setting and fiddled with things before giving up and posting here.

dandev91

This worked, but now I face another issue I haven't seen before - which I will post another forum message for.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335