这些都不是必要的,尤其是重复表格。
介绍
自从关系数据库建模标准(IDEF1X)已经普及使用超过25年(至少在高质量、高性能市场上),我使用这个术语。Date & Darwen,1虽然他们一直致力于关系模型的推进和压制,但他们直到2009年我向他们介绍IDEF1X时才知道它,因此有了新的术语2用于我们已经使用数十年的标准术语。此外,新术语并不能涵盖所有情况,而IDEF1X可以。因此,我使用已经确立的标准术语,避免使用新术语。
注意事项
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 术语中,基类型应该有一个鉴别器列,用于标识存在于其上的子类型行。
我将详细介绍每个方面。
鉴别器列需要一个 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
不应该破坏引用。
这里的问题有两个方面。 首先,您需要在关系数据库和开放式架构标准的其他领域进行基本教育。 再次强调,最好在此处开启一个新问题,以便提供有关关系数据库其他领域的完整答案。
好的,简短的回答,实际上应该在另一个问题中 如何保护排他子类型中的鉴别器免受无效更新的影响?
- 清晰度。 是的,参考完整性并不意味着“检查插入时引用是否有效,然后忘记它”。 我也没有说过它意味着那个。
- 原问题涉及子类型的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
,因此数据库中不会有任何无效的鉴别器。
UPDATE
更改超类型的鉴别器值。如何防止这种情况?采用在子类型中使用外键和重复鉴别器列似乎可以解决这个问题。 - Sam