If you are merging two or more database records from table X, that have a many-to-many relationship with table Y, how should you solve the problem of records in intermediate table Z that would violate one or more constraints as a result of the merge? Do you simply discard them or break off the transaction?
It depends on the result which you are expecting. In general you also have to merge the table Z to keep the data integrity.
Discarding records which are violating constraints should not happen. It would be annoying if those lost records were your monthly salary payment advice.
Personally I’d drop the relations in table Z, then drop the relevant records from X, merge the records and then rewrite the new ones into the database. This means you don’t break any constraints, don’t need to disable constraints and you’ll be less likely to make merging mistakes by leaving old data behind.
There are different ways this problem could happen:
1-Table Z has bad data (not sure why or how it got in in the first place). It then becomes a business decision as what to do about the bad data. You generally would need to report this (bad) data to business and let them decide. Then you choose an appropriate approach to solve the problem.
2-If your table X or table Y uses automatically generated IDs and your table Z data is referencing those IDs, you may get this problem when you try to load table Z (assuming you have already loaded all rows in X and Y successfully). In this case, you load table Z without the FK constraint forced (relax the constraint before loading Z), load Z, and then re-build the constraint on a separate step.
As a developer or DBA, you don’t throw away production data without explicit user agreement. Also, Allows keep backups.