Εquilibrium

Order of Insert, Update, Delete when updating a database using a dataset with multiple related tables

There are two cases that uniquely specify the order of actions that must be performed:

1. Deletion of records must occur before inserts
   Why: If a table has a column with unique values, and in the dataset you delete a row and then reinsert it with the same value for the unique column, then the new row cannot be inserted without deleting first the old row.
2. Modification of existing records must occur after inserts
   Why: If in the dataset you insert a new row in a parent table and then update a record from a child table to refer to the newly inserted parent row, you cannot update the child row in the database if the parent row not yet exists.

Conclusion:

The only possible order of actions (generally speaking, if we want to allow all types of actions when updating the dataset) is:

1. Delete (starting with children tables first, continuing up to the parent tables)
2. Insert (starting with the parent tables first, continuing down to the children tables)
3. Update (starting with the parent tables first, continuing down to the children tables)

Related: How to update a dataset with related tables and identity columns from a Windows Forms application by using a Web service in Visual Basic .NET