Deleting data with existing foreign key constraints

[darrix]

Hi, I have a Sql Server database where I need to populate the data in a set of lookup tables but there are foreign key constraints that are defined on the tables.  There are no existing references to the tables yet--but when a "DELETE DATA & APPEND" step is executed, a TRUNCATE is being called and the constraints prevent the TRUNCATE from executing.  The Pre- and Post-Create statements are specific to a create operation and so I can't disable the the constraints there.   I don't see a way to specify that the TRUNCATE be executed as a DELETE statement, either.

Is there a way to do this within Alteryx?

Thanks

[MattBSlalom · Accepted solution]

The purpose of Foreign Keys in a database are to enforce data consistency across related tables; therefore you cannot delete rows from 1 table that are referenced in another.  Either you need to first delete the related rows from the other table(s), or you need to perform an update operation instead of a delete/truncate & insert.  (Or you could drop the FKs then perform the data changes then recreate the FKs, but that would not be my first suggestion.)

The Write Data In-DB tool you're currently using doesn't have an Update option.  You can use that tool to write your data to a new table, then use a tool like the Dynamic Input In-DB tool or the Input Data / Output Data / Dynamic Input Data tools (not in the In-Database palette) to perform an Update SQL script sourcing from your new table.