Databases
StackOverflow2013
Postsdbo
POHow to properly handle update/delete operations in a RDBMS?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to properly handle update/delete operations in a RDBMS?
    primarykey
    data
    text
    <p>I recently designed my first relational database in MySQL, using InnoDB as the engine for all of my tables. I would like to know how to properly handle <code>DELETE</code> and <code>UPDATE</code> operations when you have many-to-one relationships.</p> <p><strong>Example</strong></p> <p>Let's say you have the following tables:</p> <p><strong>t1</strong><br> PK&nbsp;&nbsp;&nbsp;&nbsp;t1_ID<br> FK&nbsp;&nbsp;&nbsp;&nbsp;t2_ID<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A</p> <p><strong>t2</strong><br> PK&nbsp;&nbsp;&nbsp;&nbsp;t2_ID<br> FK&nbsp;&nbsp;&nbsp;&nbsp;t3_ID<br> FK&nbsp;&nbsp;&nbsp;&nbsp;t4_ID</p> <p><strong>t3</strong><br> PK&nbsp;&nbsp;&nbsp;&nbsp;t3_ID<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B</p> <p><strong>t4</strong><br> PK&nbsp;&nbsp;&nbsp;&nbsp;t4_ID<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C</p> <p>With those tables, a cascade on delete operation is specified for the FK in <code>t1</code> and for both FKs in <code>t2</code>. If one or more records are deleted from <code>t3</code> or <code>t4</code> the corresponding records in <code>t2</code> and <code>t1</code> are also deleted.</p> <p><strong>Questions</strong></p> <p>The delete functionality that I need is the reverse. I want the following to occur:</p> <ol> <li>Delete a record in <code>t1</code></li> <li>Delete the corresponding record in <code>t2</code></li> <li>If the corresponding record in <code>t3</code> is not used anywhere else, i.e. that <code>t3_ID</code> is not anywhere else in <code>t2</code>, delete the corresponding record in <code>t3</code></li> <li>Repeat step 3 for <code>t4</code></li> </ol> <p>I also need a similar functionality for the update operations.</p> <ol> <li>If the record in <code>t3</code>/<code>t4</code> has no other entries in <code>t2</code>, then the value can simply be changed.</li> <li>If the record in <code>t3</code>/<code>t4</code> is in at least one other record in t2, a new record will need to be created in <code>t3</code>/<code>t4</code> and the reference in <code>t2</code> will need to be updated.</li> </ol> <p>How can I achieve this?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTehTechGuy
    1. USTehTechGuy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to properly handle update/delete operations in a RDBMS?
      1. USNoChance
      1. VTFavorite
    2. VO
      singulars
      1. POHow to properly handle update/delete operations in a RDBMS?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODeleting all the references in `t2` doesn't necessarily make the items in `t3` and `t4` irrelevant. You might add new references to them later. E.g. if `t3` and `t4` are car parts, and `t1` is car models, you might discontinue all models that use them, but add a new model in the future that uses the same parts.
      singulars
      1. POHow to properly handle update/delete operations in a RDBMS?
      1. USBarmar
    2. CO@Barmar, that's a very good point. I did not think of that. In my actual situation, I am storing measured data points. In certain cases, the values will be the same, but I will also have a number of situations where having repeated values is unlikely. I figured it would be "best" to clean up the database, rather than leave the data in there, on the off chance that it will be used again. Note that this becomes especially important for one table, as those values are used to populate fields in an application that I have developed, thus fields without corresponding data should be removed.
      singulars
      1. POHow to properly handle update/delete operations in a RDBMS?
      1. USTehTechGuy
    3. COThe real answer to much of this is that it depends on the application and what the data represents, there's no general answer. Especially the part about handling updates: whether you should create new entries or modify the old ones depends on the semantics of the data.
      singulars
      1. POHow to properly handle update/delete operations in a RDBMS?
      1. USBarmar
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload