问题描述
在关系数据库设计中，是否应该担心一个（或多个）循环图出现问题？（简体）例如，表
T1（ T1_Id ，... ）
T2（ T2_Id ，T1_Id_Fk，...）
T3（ T1_Id_Fk，T2_Id_Fk ，..）主键以粗体显示。
T1中的行具有双重作用。 T1行r1可以与T2中的行r2成关系T3，但也可以是T2中（可能相同）行r2'的父行。这两个关系是正交的。
我想出了这样的事情：
$ b T1_Base（ T1_Id ，...）
T1_Child1（ T1_C1_Id ，...）
T1_Child2（ T1_C2_Id ...）
T2（ T2_Id ，T1_C1_Id_Fk，...）
T3（ T1_C2_Id_Fk，T2_Id_Fk ，... ）
其中我们在T1_Base和T1_Child1和T1_Child2之间分别有一对一的关系，以消除这里描述的一些可能的级联问题，但我仍然得到一个周期。
我应该甚至担心这个在每个FK定义为ON CASCADE NO ACTION的情况下吗？
外键）约束是定向的。 FK声明是表格的子值的声明。列表显示为一些其他被引用的表格的子值。列列表。当人们谈论FK周期时，他们意味着FK参考的周期全部放在头尾。
您在这里似乎没有任何这样的周期。 >
$ b
（表表示应用程序关系/关联）FK约束通常称为关系，尽管它们实际上只是关于在每个数据库状态。尽管每个FK都有一个关联的查询表达式来表示关联关系/关联。）
这样的循环没有任何逻辑上的问题。发生这种情况时，这些表都具有与那些superkey / UNIQUE列表完全相同的一组subrow值。 （事实上，在每一对表之间都有一个双向约束）。在所有FK列表都相同（相同名称，相同顺序）和所有非FK列不同的简单情况下，这意味着您可以使用单独的表格，只需要一个表格即可。否则，在合适的列重新命名之后，您仍然可以使用一个表。
但是许多DBMS无法处理正在声明的FK引用循环，因为FK声明对级联并且数据库管理系统设计人员还没有为设计人员提供一个设施来说明什么时候在循环中发生级联应该发生。所以，如果你不想要一个单一的表格设计，那么你必须通过删除一个声明性的FK约束来删除这个循环。虽然你可以通过一个触发器强制约束，这是SQL DBMS中唯一可用的约束工具。
PS由于你的第一个设计可能有 T3（T1_Id_Fk，T2_Id_Fk）引用T2（T1_Id_Fk，T2_Id）和 T2（T1_Id_Fk）引用T1（T1_Id），你的第二个设计可能不是正确约束。
In relational database design, should one worry about one (or more) "cyclic graphs" posing problems?
(Simplified) E.g., tables
T1(T1_Id, ...)
T2(T2_Id, T1_Id_Fk, ...)
T3(T1_Id_Fk, T2_Id_Fk, ..)
Primary keys are bolded.
Rows in T1 have a double role. A T1 row r1 can be in relationship T3 with a row r2 in T2, but it can also be a parent row for a (possibly the same) row r2' in T2. These two relationships are orthogonal.
I came up with something like this:
T1_Base(T1_Id, ...)
T1_Child1(T1_C1_Id, ...)
T1_Child2(T1_C2_Id, ...)
T2(T2_Id, T1_C1_Id_Fk, ...)
T3(T1_C2_Id_Fk, T2_Id_Fk, ...)
where we have one-to-one relationships between T1_Base and T1_Child1 and T1_Child2, respectively, to eliminate some of the possible cascading issues described here Relational database design cycle, but I still get a cycle.
Should I even be worried about this in a context where every FK is defined with ON CASCADE NO ACTION?
A FK (foreign key) constraint is directed. A FK declaration is a statement that subrow values for a table & column list appear as subrow values for some other "referenced" table & column list. When people talk about FK "cycles" they mean cycles of FK references all put head to tail.
You don't appear to have any such cycles here.
(Tables represent application relationships/associations. FK constraints are often called "relationships" although they are actually just statements about tables that are true in every database state. Although every FK has an associated query-expressible table representing an associated relationship/association.)
There's no logical problem with such cycles. When that happens, the tables all have exactly the same set of subrow values for those superkey/UNIQUE column lists. (Indeed there is a 2-way constraint between every pair of tables.) In the simple case where all the FK columns lists are the same (same names, same order) and all non-FK columns are different, it means that instead of the separate tables you could use just the one table that is their join. Otherwise, after suitable column renamings you could still use just one table.
But many DBMSs can't handle FK reference cycles being declared, because FK declarations do double duty for cascades on update, and the DBMS designers haven't offered a facility for the designer to say what order cascades should happen in when there's a cycle. So if you don't want a one-table design then you are forced drop the cycle by dropping one of the declarative FK constraints. Although you can enforce the constraint via a trigger, which is the only general constraint facility available in SQL DBMSs.
PS Since your first design probably has T3 (T1_Id_Fk, T2_Id_Fk) references T2 (T1_Id_Fk, T2_Id) and T2 (T1_Id_Fk) references T1(T1_Id), your 2nd design is probably not properly constraining.
这篇关于关系数据库设计 - “循环”图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！