community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Help on foreign key constraints issue while updating table

Meteor

Hi All -  Can some one help or update if you have faces this scenario and how to handle this with tools in alteryx. I have created couple flows for insert/update a target table in postgres . And the second flow to delete records from the same target table by referring to primary keys to be removed from a temporary table that i create within the output tool.

 

It works if there are no references of the primary key values in other tables. All three flows are good. However it gives me violation error for foreign keys . Can you please suggest how we can make sure we take care of all delendent tables prior to deleting or updating my target table. and which tools can be added in the below workflow in order to perform that.

 

Any references r guidance with tool names and options that i can do will help

 

 

thanks in advance !

Alteryx
Alteryx

@gmv Have you looked at pre/post SQL statements? This might solve the issue for you. In the pre-sql statement, you can take care of all dependent tables prior to updated/deleting the target table. 

Meteor

thanks , Let me try that and get back

Meteor

Hi - I will not be able to remove dependent table records in pre sql as i create the temporary table in the output tool which will actually store the records to be deleted and referencing that in post sql to delete my target table. 

 

So i will not have the track of records to be deleted prior to creating this temporary table and i will need the output tool's post sql option to do any delete. Hence I'm not sure of the approach .

 

Please suggest !

Alteryx
Alteryx

@gmv  Are you able to create a permanent table and drop it in the post SQL statement?

 

Otherwise, you might have to do everything in the pre-sql statement before processing. Another thing you can do is create a stored procedure that does this and call it in the pre-sql statement (only works with SAP Hana, Oracle, SQL Server at the moment).

Highlighted
Meteor

I have added a output tool which will create a permanent temporary table ( or however you want to call it ) with the option as (Overwrite table (drop)) and then im inserting the records that are marked for deletion into this,

 

In the same output tool i have the post sql wherein i do a delete from my target table referencing the primary keys from this temporary table. I did not write explicit drop of this tabel again because i chose the option as overwrite (drop) while creating it.

 

So do you have any other suggestions to take care of my dependent tables , this is postgres database for your reference 

thanks very much

Labels