不使用存储过程、触发器或UDF实现超类型和子类型数据完整性的强制执行

Question

我经营着一家小型食品生产企业，需要管理客户订单。我已经建立了一个关于我的业务方面的概念数据模型，但我需要一些指导来完全在RDMS中实现它。

首先，我提出了下面给出的逻辑模型。由于我的数据建模知识有限，因此我的图表可能存在错误，但希望它能传达我的意图。请注意，这只是更大模式的简化部分，为了简单起见，我仅呈现相关表格。

• 一个客户订单可以有一个或多个订单项
• 订单项可以是FoodItem或ComboItem之一
• ComboItem是两个或多个FoodItem的逻辑分组

我已经在MySQL中实现了上述模式，并编写了几个小程序来填充客户订单表格。这完成了工作，但没有考虑数据完整性。我已经注意到，在当前实现中，某些数据完整性规则没有在数据库级别强制执行。

例如，FoodItem是OrdrItem的子类型。对于每个FoodItem行，必须有一个对应的OrdrItem行。然而，在当前实现中，我可以删除一个FoodItem行，从而使OrdrItem表中留下一个没有对应子类型表中行的行。这应该被禁止。

一些进一步的数据约束：

• 订单必须至少有一个关联的“订单项”（即订单不能为空）
• 订单项必须是精确地与FoodItem或ComboItem之一相关联的子类型（即，订单项不能既是FoodItem又是ComboItem）。
• 未来可能会出现一些进一步的约束条件

我希望这些数据完整性规则被嵌入到数据库中，这样我就不必担心它们在每个新客户端应用程序、临时脚本或劣质SQL语句中的强制执行。我怀疑如果我不在数据库级别上做出这些保证，我很有可能在未来遇到数据完整性问题。

我的问题是：我只有对存储过程、触发器和用户定义函数的模糊知识。我有这些功能可以帮助我实现我想要的东西。然而，如果我能够使用检查约束、外键和相对简单的功能完成工作，我将非常愉快地采取这种路线。基本上，我想尽量减少复杂性，如果没有保证，不引入所有花哨的数据库特性。是否可能在不使用存储过程、触发器、用户定义函数和其他更深奥的数据库特性的情况下确保我想要的数据完整性？我愿意使用MySQL或Postgresql来实现我的解决方案，因为我对这两个系统都有基本的工作知识。

最后，如果这种对数据完整性的处理被认为过于严格，或者存在更加实用但略有不完美的解决方案，我也愿意接受。

- ntwk

---

绝对不是标准的订单数据模型。标准的订单数据模型应该只有一种类型的order_item，它会引用不同的产品类型，比如食品或套餐。 - Neil McGuigan

对于这个问题提问的精细，我给你点赞。简短的答案通常是肯定的，但可能无法适用于您当前的模式 —— 那些（ordrID、OrdrItemID）元组不是一个好主意，而字段名 fkey 真的令人感到可怕。我希望您能找到一位志愿者去详细阐述这个问题（并且，很可能是作为单独的问题）。如果没有（即使有），我认为您应该寻求一些专业支持，至少可以帮助您正确地建模。 - Denis de Bernardy

@Denis，感谢您发现我模式中的问题。经过仔细检查，我最初发布的模式图中存在许多错误，主要涉及各种表中不应该存在的列。我已经整理好了，并希望看起来更加合理。 - ntwk

Answer 1

不幸的是，“现代”的数据库管理系统不直接支持ER图中可以放置的所有花哨符号¹。实际上，物理外键所强制执行的只是子行不能存在于父行之外，这将得到一个普通的"1对0或N"关系²。

您可以...

• 使FK可为空，以将关系的左侧变形为“0或1”
• 和/或在FK上面放置一个键，将右侧变形为“0或1”

...但这大概就是您可以“开箱即用”的全部了。

要强制执行其他规则³，您将不得不显著地“丑化”模型并可能使用延迟约束⁴，或者您可以使用过程代码⁵来实现。

虽然您关于尽可能将完整性规则放在数据库本身中的直觉是完全正确的，但仍然被认为是较小的恶，仅在过程代码中强制执行“不寻常”的情况，而不是扭曲数据模型以适应声明性约束的限制。

事实上，最流行的技术之一是创建一个“API”：

禁止客户直接修改表（通过撤销适当的权限）

只允许他们通过你编写的存储过程修改数据，并强制执行所有必要的业务规则⁶。这样，你将所有客户都导向同一个“清算中心”，没有人可以行为不端。

但这是一个相当“重量级”的解决方案，如果情景足够简单，可能不值得麻烦。如果你的应用程序是唯一一个会修改数据库的，那么仅在客户端代码中实现规则可能就足够了...

---

¹ 例如继承（也称为子类型、类别、泛化层次结构）。

² 左侧：任何给定的子项必须有“1”个父项。右侧：任何给定的父项必须有“0或N”个子项。

³ 例如孩子的互斥性和存在性，这对于继承非常重要，正如您已经注意到的那样。

⁴ 这在PostgreSQL中受支持，但不在MySQL中。

⁵ 按优先顺序：

• 触发器和存储过程
• 中间层
• 或客户端。

⁶ 但要注意竞态条件：事务隔离将保护您免受其中一些影响，但不是全部，您可能需要进行一些显式锁定。

- Branko Dimitrijevic

---

@a_horse_with_no_name 不，这个“独占性”是另外一回事。它与继承有关，当作为“每个类在自己的表中”实现时。在这种情况下，“逻辑对象”由两行表示：一个在父表中，另一个在一个子表中。不应该有多个在不同子表中引用父表中相同行的行。相关概念是“存在”，意味着父表中不能没有子表中的行。请参见脚注3中的链接，以声明方式强制执行这两个条件。 - Branko Dimitrijevic

@a_horse_with_no_name 而且正如我之前提到的，PostgreSQL继承功能实现不完整，使得它的实际使用价值相当有限。 - Branko Dimitrijevic