CODEWITHSUNDEEP
mysqlsqldatabase-designsql-delete
Jean-François Côté
Jean-François Côté

Reputation: 4350

How to handle the deletion of future orphan records in mySQL

I have a database that is designed like this:

Tables:

| Snapshot |
 * id (pk)

| Detail |
 * id (pk)
 * snapshot_id (fk_delete_cascade)
 * stat1_id (fk)
 * stat2_id (fk)
 * stat3_id (fk)
 * stat4_id (fk)

| Stats |
 * id (pk)

The problem is that when I delete a Snapshot, the details are automatically deleted because of the delete cascade but related stats are not because the relation is not in the good direction (the details know the stats but not the other way around). This creates a TON of orphan records.

Is there an easy way to deal with this that I don't see or do I really need to do a query that delete the orphans stats afterward when they are not used by any of the 4 fields of detail?

Thanks!

Upvotes: 1

Views: 224

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

The way to ensure consistency is to reverse the direction of reference.

Instead of making Detail have 4 fk's to Stats, create an fk in Stats referencing the Detail.

Stats
* id (pk)
* detail_id (fk)

Then you can make that fk cascading, and deleting a Detail will automatically delete all related Stats.

But this doesn't work if one Stat may be referenced by many Details. In that case, you don't have a way to automate deletion by a cascading constraint. You have to clean up orphans periodically.

DELETE s FROM Stats AS s LEFT OUTER JOIN Detail AS d
  ON s.id IN (d.stat1_id, d.stat2_id, d.stat3_id, d.stat4_id)
WHERE d.id IS NULL;

Unfortunately, this is impossible to optimize because of the multiple columns in Detail. It must do a very expensive table-scan to try to find related Detail rows.

So if you can reverse the fk reference like I described above, that would be better.

Upvotes: 1

Related Questions