主页 / dba / 问题 / 344880
Accepted
Carl Schmidt
Asked: 2025-01-23 08:48:39 +0800 CST

SQL Server：如何实现具有级联的三向关系

  • 772

这是我正在使用的架构：数据库设计

以下是在https://dbdiagram.io/d中重新创建的 DBML ：

Table University {
  ID integer [primary key]
}

Table Professor {
  ID integer [primary key]
  UniversityID integer [primary key, ref: > University.ID]
}

Table Class {
  ID integer [primary key]
  UniversityID integer [primary key, ref: > University.ID]
  ProfessorID integer [null]
}

Ref: Class.(ProfessorID, UniversityID) - Professor.(ID, UniversityID)

我的目标：

  • 一所大学可以有多个班级和教授。
  • 一个班级可以有一位教授，或者没有教授。
  • 删除一所大学会删除其所有教授和课程
  • 删除教授会将 Class.ProfessorID 设置为 NULL

最后一个目标给我们带来了问题。由于存在多个级联路径，SQL Server 会阻止添加另一个级联外键约束，并且触发器无法工作，因为：

  1. 由于 Professor 上的外键约束，删除查询被拒绝，因此 FOR DELETE 触发器永远不会运行
  2. 由于与大学的级联关系，SQL 无法创建 INSTEAD OF DELETE

我怎样才能实现这个目标？

database-design

1 个回答

  1. Best Answer

    老实说，正确的方法是使用存储过程。

    我对实际数据库设计的经验是，声明性约束的使用非常适度（即使声明了，也经常不检查外键），而触发器的使用则更少。

    原因是它们会导致引擎出现意外的反应，在 SQL 用户看来，这是一条针对一个明确表的简单 DML 语句，但却可以触碰或改变大量未在 SQL 语句中指定的相关数据。

    具有级联约束和变异触发器的数据库可能会变得非常难以调查和推理。

    如果数据库设计者使用约束，那么约束最好用于执行完整性检查，并防止SQL 用户编写的无效操作。例如，如果有人试图删除仍与课程有关联的教授，则该操作将被拒绝，因为您删除的教授仍与课程有关联。

    而且正如您已经发现的那样，即使只有三个表（即稍微复杂一些），级联通常也不能很好地组合在一起，并且通常不是一个通用的解决方案。

    同时，触发器很难设计为在所有情况下都能正常工作，尤其是那些引起变化的情况（从而有可能出现级联）。

    因此，我的建议是将工作转移到存储过程中，如果删除了一位教授，则首先检查该教授是否被任何班级引用 - 如果是，则在继续删除教授之前明确中断这些链接。

    稍后阅读此存储过程的某个人（包括您自己）将会准确地了解当删除教授时发生了什么。

    使用存储过程还可以更好地控制操作顺序以及在负载较重的数据库中可能调整的许多其他设置。

    级联和触发器的使用通常看起来非常普遍，但根据我的经验，它们实际上适用的情况远远少于它们看起来适用的情况。

    我认为关键的是，新手倾向于尝试使用它们来分而治之复杂性，避免必须写出明确的程序（或记住必须进入该程序的所有内容才能使其正确）。

    学生在其他语言中接受的培训有时会鼓励这样做，例如在通用或面向对象语言中将大型方法分解为较小的方法。但这种类比并不适用于在 SQL 中使用约束和触发器，SQL 中的单个过程长达几页是很正常的。

    • 1

相关问题