mysql - 如何处理 mySQL 中 future 孤立记录的删除

Question

我有一个这样设计的数据库:

表格:

| 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)

问题是当我删除一个Snapshot时，由于delete cascade但是相关的stats，details被自动删除 不是，因为关系不是好的方向(细节知道统计数据但不是相反)。这会产生大量孤立记录。

有没有一种我看不到的简单方法来处理这个问题，或者我真的需要做一个查询来删除孤儿 stats ，当它们没有被任何 4 个使用时详细信息的字段？

谢谢!

Answer 1

保证一致性的方法是反转引用的方向。

不是让 Detail 有 4 个 fk 到 Stats，而是在 Stats 中创建一个引用 Detail 的 fk。

Stats
* id (pk)
* detail_id (fk)

然后你可以让那个fk级联，删除一个Detail会自动删除所有相关的Stats。

但是如果一个 Stat 可能被许多 Details 引用，这就不起作用了。在那种情况下，您无法通过级联约束自动删除。你必须定期清理孤儿。

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;

不幸的是，由于 Detail 中的多列，这无法优化。它必须执行非常昂贵的表扫描以尝试查找相关的详细信息行。

所以如果你能像我上面描述的那样反转 fk 引用，那就更好了。