Delete matching rows with a foreign key

Hello EM team,

I tried to look for it but not sure if you can answer this quickly. I have a table that has a primary key and a foreign key. I’m trying to delete matching rows so that I can do a upload since I and do an update but a full insert. When I try to delete the matching row it complains of my foreign key. Whats the best way to turn off the constraint then renable it after the delete?

Thanks
Flowy

Hello Flowy,

You can add the “Database command” action with a custom SQL before and after that “Delete matching rows” action. The first “Database command” action should remove the foreign key constraint and the second action should add that constraint back.

But you have to be sure that nobody else will add a row with an incorrect foreign key between those two actions. Also, it’s better to move the “Delete matching rows” action to a submodule and call that submodule with the “Call another module or project” action in the “Capture errors and continue” mode in order to be sure that the foreign key constraint will be restored even if the “Delete matching rows” action will fail with an error.

Hi Andrew!

Man your awake late!

Anyhow… Yes easymorph runs should be the only thing that is importing into these tables. Currently we have 3 tables. Main Table A that links one key off Table B and then another Table C that links to B. The logic was, delete the main table A, the reinsert those tables, then Clear table C, clear table B, import into table b, and the import into Table C.

I see the database command and changed it to custom command. I’ll play around with how the statement should look in there.

What do you mean by a submodule? Does having a submodule then mean if the submodule fails, then it will still continue with the rest of commands? Let me see how to do that.

Thanks
Flowy

Flowy, I’m in the EET timezone :slight_smile:

As for the submodule question: both “Call another module or project” and “Iterate” actions have the “Capture errors and continue” mode. In that mode calling action will not fail in case of the called module failure. Instead, it will capture the error message and return it in the result dataset.