Primary Key required for Update option, but Primary Key exists in Oracle
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Output
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
