Thursday, July 1, 2010

How to duplicate a row of a database table, with foreign key dependencies

I was required to duplicate a row of a database table, with foreign key dependencies. It is not a trivial problem as the dependent rows of the foreign key tables have to be created, and then the values of these ids taken and the fields updated in the primary table. Of course, the dependent rows will likely also have foreign key fields so their dependent table rows have to be created first. This ripple effect can go on and on.

This is hard to do in sql and thinking about it, it would be easier to do using recursion in a programming language.

Steps:

write a recursive method
NewId createRow() {
  for each dependent foreign key field {
   newId = dependent table.createRow()
   update foreign key field with newId
  }
}

"for each dependent foreign key field": this list can be obtained using JDBC.

No comments: