Several possibilities are discussed in the article Working with Foreign Keys: Writing Database Tables.

i'd look at using FeatureWriters. Once the first table has had the features written, you can use the summary output to trigger the next FeatureWriter and so on

If only the order is important, you can use as many readers as there are dependency levels in your data model. One reader for tables with no foreign key constraints, another reader for tables with a foreign key, another reader for tables with a foreign key that points to a table with a foreign key, and so on. Order readers in the workspace (to force write order) and use a single FeatureWriter transformer.

On the other hand, if you need to retrieve the primary keys of the inserted tables, you can chain a Feature Writer and FeatureReader and then use a FeatureMerger to assign created primary keys to the child tables.

Hi @tnarladni​ , which one are you using to insert records into the tables, regular writer, FeatureWriter, or SQLExecutor?

It would be helpful for thinking of a possible solution, if you could post screenshots illustrating the current workflow and your desired writing order.

Thinking thru this...i would take all the features from failed port of the tester in path 3 and write that to the table as it would not violate constraint. all passed features will go through the statistics calculator in path 3 and the summary data is loaded to table 3 via a feature writer. the Complete data would need to be feature merged with the feature writer's result otherwise there's not a guarantee they wont load first. Let me know if i'm on the right track?

I think @hkingsbury​ 's approach could solve the problem easily. This screenshot illustrates how you can wait for writing table1, table2 and table3 to complete before starting to write table4.

except if all records failed the tester in path 3, merge would not work. but i would still want all records loaded in table4

So give table 3 it's own FeatureWriter.