如何在子类型中实现引用完整性

Question

如何在子类型中实现引用完整性

sqldatabaserelational-databaseforeign-key-relationship

9

我在关系型数据库中有以下表格：

[Sensor]
LocationId [PK / FK -> Location]
SensorNo [PK]

[AnalogSensor]
LocationId [PK/FK -> Sensor]
SensorNo [PK/FK -> Sensor]
UpperLimit
LowerLimit

[SwitchSensor]
LocationId [PK/FK -> Sensor]
SensorNo [PK/FK -> Sensor]
OnTimeLimit

[Reading]
LocationId [PK/FK -> Sensor]
SensorNo [PK/FK -> Sensor]
ReadingDtm [PK]

[ReadingSwitch]
LocationId [PK/FK -> Reading]
SensorNo [PK/FK -> Reading]
ReadingDtm [PK/FK -> Reading]
Switch

[ReadingValue]
LocationId [PK/FK -> Reading]
SensorNo [PK/FK -> Reading]
ReadingDtm [PK/FK -> Reading]
Value

[Alert]
LocationId [PK/FK -> Reading]
SensorNo [PK/FK -> Reading]
ReadingDtm [PK/FK -> Reading]

基本上，ReadingSwitch和ReadingValue是Reading的子类型，SwitchSensor和AnalogSensor是Sensor的子类型。读数可以是SwitchReading或ValueReading值 - 不能同时是两者，传感器可以是AnalogSensor或SwitchSensor。

到目前为止，我遇到的唯一方法是here。

肯定有更好的方法来做这种事情。

我能想到的另一种方法是不使用子类型，而是完全展开所有内容：

[SwitchSensor]
LocationId [PK/FK -> Location]
SensorNo [PK]

[AnalogSensor]
LocationId [PK/FK -> Location]
SensorNo [PK]

[SwitchReading]
LocationId [PK/FK -> SwitchSensor]
SensorNo [PK/FK -> SwitchSensor]
ReadingDtm
Switch

[AnalogReading]
LocationId [PK/FK -> AnalogSensor]
SensorNo [PK/FK -> AnalogSensor]
ReadingDtm
Value

[AnalogReadingAlert]
LocationId [PK/FK -> AnalogReading]
SensorNo [PK/FK -> AnalogReading]
ReadingDtm [PK/FK -> AnalogReading]

[SwitchReadingAlert]
LocationId [PK/FK -> SwitchReading]
SensorNo [PK/FK -> SwitchReading]
ReadingDtm [PK/FK -> SwitchReading]

这可能并不那么糟糕，但我也有引用Alert表的表格，因此它们也必须被复制：

[AnalogReadingAlertAcknowledgement]
...
[AnalogReadingAlertAction]
...
[SwitchReadingAlartAcknowledgement]
...
[SwitchReadingAlartAction]

等等。

这个问题有人能理解吗？？

- Mark

2个回答

1

第二个选项也存在问题 - 例如对于传感器（假设SensorNo是一个代理键），因为你没有基础表，所以在子类表之间，SensorNo代理键要么不唯一，除非你使用一个笨拙的机制来跨所有子类表发放键（或使用GUID）。

如果您有用户界面“组合”不同类型的传感器，例如显示模拟和开关传感器的联合列表，则这种情况会更加复杂。

我建议您坚持第一种模式，然后使用经过充分测试的事务性代码封装这些表的插入和维护。

例如，为各个子表创建插入过程，在工作单元下插入相应的基础和子类记录。您还可以进一步通过撤销任何表的INSERT权限来强制通过SPROC进行插入。

您还可以运行每日完整性报告，检查是否存在违反继承结构的情况。

- StuartLC

1

这是一个关系型数据库，而不是一堆对象类/子类。尽管Fowlers所说的有重要的区别。同意您关于安全性的观点。 - PerformanceDBA

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- PerformanceDBA · Accepted Answer

这些都不是必要的，尤其是重复表格。

介绍

自从关系数据库建模标准（IDEF1X）已经普及使用超过25年（至少在高质量、高性能市场上），我使用这个术语。Date & Darwen，¹虽然他们一直致力于关系模型的推进和压制，但他们直到2009年我向他们介绍IDEF1X时才知道它，因此有了新的术语²用于我们已经使用数十年的标准术语。此外，新术语并不能涵盖所有情况，而IDEF1X可以。因此，我使用已经确立的标准术语，避免使用新术语。

即使是“分布式键”的概念也无法识别底层的普通PK::FK关系、它们在SQL中的实现以及它们的强大功能。
关系模型，因此IDEF1X的概念是标识符和迁移。
当基础知识被理解时，供应商可能不完全掌握情况，例如“部分索引”等奇怪的事情，但著名的“学者”和“理论家”提出不完整的新概念，而该概念在25年前已经被标准化并得到充分处理...这是出乎意料和不可接受的。

注意事项

IEC/ISO/ANSI SQL几乎不能很好地处理Codd的3NF（Date和Darwen的“5NF”），并且根本不支持Basetype-Subtype结构；这方面没有声明性约束（但应该有）。

因此，为了执行数据模型中表达的完整规则集，包括Basetype :: Subtype和Subtype :: Basetype，我们必须通过一些方法来使用CHECK CONSTRAINT等（我避免使用触发器，原因有很多）。

然而，我考虑到了所有这些。为了在Stack Overflow上有效地提供数据建模服务，而不必先进行全面的讲解，我有意提供可以由有能力的人使用现有的SQL和约束实现的模型，以任何他们需要的程度。它已经被简化了，并包含通常的强制执行水平。

我们可以同时使用链接文档中的示例图形和您完全符合IDEF1X标准的Sensor Data Model。

不熟悉关系建模标准的读者可能会发现IDEF1X Notation有用。认为数据库可以映射到对象、类和子类的读者应注意，继续阅读可能会导致受伤。这比Fowler和Ambler阅读的更进一步。

实现基类型-子类型的引用完整性

基类型-子类型结构有两种类型。

独占式子类型

独占式意味着每个基类型行必须有且仅有一个子类型行。在 IDEF1X 术语中，基类型应该有一个鉴别器列，用于标识存在于其上的子类型行。

对于超过两个子类型，这是必需的，并且我实现了一个鉴别器列。
对于两个子类型，由于这可以从现有数据中轻松推导出来（例如，Sensor.IsSwitch 是 Reading 的鉴别器），因此我不为 Reading 建模额外的显式鉴别器列。但是，您可以自由地按字面标准实现鉴别器。

我将详细介绍每个方面。

鉴别器列需要一个 CHECK CONSTRAINT 来确保它在值范围内，例如：IN ("B", "C", "D")。 IsSwitch 是一个 BIT，它是 0 或 1，因此已经受到限制。
由于基类型的 PK 定义了其唯一性，只允许一个基类型行；不允许插入第二个基类型行（因此也没有第二个子类型行）。

因此，在基类型中实现类似于（PK，Discriminator）的索引是过度设计、完全冗余和不必要的，就像您提供的链接建议的那样。唯一性在PK中，因此PK加上任何内容都将是唯一的。

IDEF1X不需要在子类型表中添加鉴别器。在子类型中，再次受其PK的唯一性约束，按照模型，如果在该表中将鉴别器实现为列，则其中的每一行都将具有相同的鉴别器值（每个书都将是“B”；每个ReadingSwitch都将是IsSwitch）。因此，在子类型中实现鉴别器作为列是荒谬的。再次强调，实现类似于（PK，Discriminator）的索引是完全冗余和不必要的：唯一性在PK中，因此PK加上任何内容都将是唯一的。

链接中标识的方法是实施参照完整性的笨拙和臃肿（为了没有目的而进行大量数据复制）方式。作者可能从未在其他地方看到该结构的一个很好的原因。这是一种基本的SQL理解失败，并有效地使用它。这些“解决方案”是遵循“SQL不能做…”教条的人典型的产物，因此对SQL能够做什么视而不见。由Fowler和Ambler的盲目“方法”导致的恐怖事件更糟糕。

子类型 PK 同时也是基类型的 FK，这就足以确保子类型没有父基类型是不存在的。

因此，对于任何给定的 PK，无论插入哪个基类型-子类型，都将成功；而在此之后尝试插入的任何基类型-子类型都将失败。因此，在子类型表中没有什么可担心的（防止了第二个基类型行或相同 PK 的第二个子类型行）。
.

SQL CHECK CONSTRAINT 仅限于检查已插入的行。我们需要检查已插入的行与其他行是否匹配，无论是在同一张表中还是在另一张表中。因此，需要使用“用户定义”的函数。

编写一个简单的UDF，用于检查Basetype中PK和Discriminator的存在性，并返回1（如果存在）或0（如果不存在）。每个Basetype需要一个UDF（而不是每个Subtype）。
在Subtype中，实现一个CHECK CONSTRAINT，调用UDF，使用PK（既是Basetype又是Subtype）和Discriminator值。
我已经在许多大型真实数据库上，使用不同的SQL平台实现了这一点。以下是基于对象的"User Defined"Function Code和DDL Code。
这个特定的语法和代码在Sybase ASE 15.0.2上测试通过（他们对SQL标准的合规性非常保守）。
我知道每个SQL平台上“User Defined”Functions的限制都不同。然而，这是最简单的构造方式，据我所知，每个平台都允许这种方式。（不知道非SQL平台怎么做。）
当然，这个巧妙的技巧可以用来实现任何您可以在数据模型中绘制的非平凡数据规则。特别是为了克服SQL的限制。请注意避免双向约束（循环引用）。

因此，子类型中的CHECK CONSTRAINT确保PK加上正确的鉴别器存在于基类型中。这意味着只有该子类型（PK）存在于基类型中。

任何尝试插入另一个子类型（即违反排他规则）的后续尝试都将失败，因为PK+鉴别器不存在于基类型中。

同一子类型的任何后续行的插入都会由其PK约束的唯一性阻止。

仅缺少的部分是未强制执行“每个基类型必须至少有一个子类型”的规则（在链接中未提到）。这很容易在事务代码中处理（我不建议约束以两个方向进行或使用触发器），用正确的工具完成任务。

非排他子类型

基类型（父级）可以托管多个子类型（子级）

没有要识别的单个子类型。

鉴别器不适用于非排他子类型。

通过使用基类型PK在子类型表上执行存在检查来识别子类型的存在。

只需排除调用上述UDF的CHECK CONSTRAINT即可。

PRIMARY KEY，FOREIGN KEY和通常的范围CHECK CONSTRAINT足以支持所有非排他子类型的要求。

参考资料

有关更详细的信息；包括详细信息的图表概述；以及子类型和可选列表之间的区别，请参阅此子类型文档。

注

我也被C J Date和Hugh Darwen对“推进”关系模型的不断引用所迷惑。经过多年的互动，基于大量一致的证据，我得出结论，他们的工作实际上是对其的贬低。他们没有做任何事情来推进E F Codd博士的开创性工作，即关系模型，而是做了一切来破坏和压制它。
他们对关系术语有私人定义，这当然严重阻碍了任何交流。他们对我们自1970年以来一直拥有的术语有新术语，以便显得他们已经“发明”了它。

评论回复

^{所有没有评论的读者都可以跳过此部分。}

不幸的是，有些人在以极高的额外成本的方式做事情方面受到了深刻的教育，即使在明确指导正确的方式时，他们也无法理解。也许这就是为什么不能用问答格式代替适当的教育。

Sam: 我注意到这种方法不能防止某人使用 UPDATE 来更改 Basetype 的鉴别器值。怎么才能防止这种情况？使用 FOREIGN KEY 和子类型中的重复鉴别器列似乎可以解决这个问题。

是的。这种方法不能防止某人使用 UPDATE 更改键或其他不相关表中的列，也不能解决其他问题。它只回答了一个特定的问题。如果您希望防止某些 DML 命令或其他操作，请使用专为此目的设计的 SQL 工具。所有这些都超出了本问题的范围。否则，每个答案都必须涉及每个不相关的问题。

答案。既然我们应该使用自 1993 年以来可用的 开放式架构标准，所有对数据库的更改都必须通过 ACID 事务进行。这意味着禁止直接对所有表进行 INSERT/UPDATE/DELETE 操作；数据保持完整性和一致性（ACID 术语）。否则，您就会遇到混乱，例如您所举的例子及其后果。这种方法的支持者不理解事务，他们只理解单个文件的 INSERT/UPDATE/DELETE。

此外，FK+重复 D+重复索引（以及其中的巨大成本！）根本没有起到这样的作用，我不知道你从哪里得出“似乎”的结论。

dtheodor: 这个问题是关于参考完整性的。参考完整性并不意味着“检查插入时引用是否有效，然后忘记它”。它意味着“永远保持引用的有效性”。重复鉴别器+ FK方法保证了这种完整性，而您的UDF方法则没有。无疑，UPDATE不应该破坏引用。

这里的问题有两个方面。首先，您需要在关系数据库和开放式架构标准的其他领域进行基本教育。再次强调，最好在此处开启一个新问题，以便提供有关关系数据库其他领域的完整答案。

好的，简短的回答，实际上应该在另一个问题中 如何保护排他子类型中的鉴别器免受无效更新的影响？

清晰度。是的，参考完整性并不意味着“检查插入时引用是否有效，然后忘记它”。我也没有说过它意味着那个。

参照完整性意味着数据库中的FOREIGN KEY引用具有与其引用的PRIMARY KEY完整性。
声明性参照完整性意味着数据库中声明的引用... CONSTRAINT FOREIGN KEY ... REFERENCES ...
CONSTRAINT CHECK ...
由RDBMS平台维护，而不是应用程序代码。
它并不意味着“永远维护引用的有效性”。

原问题涉及子类型的RI，我已回答，并提供了DRI。

必须强调的一点是，不需要大量低效的结构和重复的表。

您的问题与RI或DRI无关。
虽然您的问题问得不正确，因为您期望方法提供方法不提供的内容，并且您不了解其他方式已经满足了您的需求，但您的问题是“如何保护独占子类型中的鉴别器免受无效的UPDATE？”
答案是使用我们应该自1993年以来使用的开放式架构标准。这可以防止所有无效的UPDATE。请阅读链接的文档，并理解它们，您的担忧是不存在的。这就是简短的答案。
但是您没有理解简短的答案，所以我在这里解释一下。

不允许任何人直接使用SQL或使用SQL的应用程序走近数据库并更改列或值。如果允许这样做，你将无法拥有安全的数据库。
所有对数据库的更新（包括多行INSERT/UPDATE/DELETE）都被实现为ACID SQL事务。仅限于事务。这些事务构成了数据库API，对使用数据库的任何应用程序都是公开的。
- SQL具有ACID事务。非SQL数据库没有事务。这些数据库系统的支持者对事务一无所知，更不用说开放式架构了。他们的非架构是一个单块堆栈。而且每个月都要重构“数据库”。
由于你编写的唯一事务将在单个逻辑工作单元中插入basetype+subtype，因此维护了basetype::subtype关系的完整性（数据完整性，而不是引用完整性），并在数据库内维护。因此，对数据库的所有更新都是有效的，不会有任何无效的更新。
由于你不会如此愚蠢地编写代码，在单个行中UPDATE鉴别器列而没有相应的DELETE Previous_Subtype，将其放入事务中，并将GRANT EXEC权限授予用户ROLES，因此数据库中不会有任何无效的鉴别器。