Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.