Ensure right ordering of tables concerning constraints

Hey guys, I created a workflow to loop over tables in my database. So far, it looks like this:

Let’s say I have a table called employee which contains a primary key. Furthermore there is a table action and task that both have foreign keys on employee

My overall goal is to perform the same actions on each table, e.g. dropping duplicates, an then inserting the tables to a new layer of my database schema. However, in this new schema, foreign keys are enabled. Therefore, I must ensure that the loop first iterates over the employee table and then over the other.

I know that this might be solved my some numeric prefix on the table name that reflects the correct sorting, but is there a more automatic way of doing this?

Hello @ThoMi,

I would control insert order by manually defining list of tables and schemas in external file. Then read that list into KNIME and connect it to loop start. If this is process that you will be doing on 50 different environments then it’s worth thinking how to implement this smarter. Something like listing primary and foreign keys and then ordering tables based on it.

Hope this helps!

Br,
Ivan

1 Like

You should also be able to read the employee table without the loop first and then use a variable connection to the loop. So employees always gets processed first
br