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

[dandev91]

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...').  Using SQL Developer / Alteryx's input tool, I was able to confirm that the primary key (with no-nullable constraints) was successfully created.

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!  

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.

[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 · Accepted solution]

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.