MySQL中的一对多、父子表循环关系
我面临这个问题：
我有一个父表和一个子表，一个父表可以有多个子表，标准故事。
这些是限制：
- 每个父母必须有至少一个孩子，
- 每个父母必须有一个最喜欢的孩子，
- 每个父母可以有一个至少-最喜欢的孩子
如何用 SQL 设计这个？
由于循环关系，我不确定是否可以使用标准父子表：
Parent table:
parentId
favouriteChildId NOT NULL
leastFavouriteChildId NULL
Child table:
childId
parentId
我正在考虑使用桥接表，但不确定如何对这些约束进行建模。
编辑：为了增加一些清晰度，这里是问题上下文的一部分：
有 Price 表（子表）和 PriceGroup 表（父表）。
PriceGroup 有多个价格，一个强制的 mainPrice (favouriteChild)，并且可以有一个ficialPrice (leastFavouriteChild)。
以下内容与该问题无关，但对上下文有一些启发： 价格根据其引用的产品进行分组，一个产品可以有多个价格 - 然后将这些价格分组为价格组，每个组需要参考主要价格和官方价格（如果有）。
I'm facing this problem:
I have a parent table, and a child table, one parent can have multiple children, standard story.
These are the constraints:
- each parent must have at least one child,
- each parent must have one favourite child,
- each parent can have one least-favourite child
How to desing this in SQL?
I'm not sure the standard parent-child tables can be used because of the circular relationship:
Parent table:
parentId
favouriteChildId NOT NULL
leastFavouriteChildId NULL
Child table:
childId
parentId
I was thinking of using a bridge table, but am not sure how to model these constraints.
EDIT: Just to add some clarity, here is part of the problem context:
There is the Price table (child), and PriceGroup table (parent).
PriceGroup has multiple prices, one mandatory mainPrice (favouriteChild) and can have one officialPrice (leastFavouriteChild).
The following is not related to the problem, but sheds some light on the context:
Prices are grouped according to Products they refer to, and one Product can have multiple prices - these are then grouped in price groups, and each group needs reference to a main price, and an official price (if there is one).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
根据您给出的业务规则，
。您的解决方案
满足 2 和 3。
但它也满足 1（因为 favouriteChildId NOT NULL 不允许创建没有子项的父记录）。
既然您已经有了上述内容，我假设您真正的问题是如何使 Child 表中的parentId 不为空。
通常，SQL 中有规定，因此您可以执行类似的操作，
在这种情况下“循环引用”不会成为问题（请参阅 DEFERRED)
Mysql 不支持它，所以你有以下选项
触发器：
可以假设在插入父记录时，已经知道最喜欢的子记录，那么您可以有一个触发器，该触发器将在父表上插入之前运行，并将
注意：问题是，通过这种方式，您可以正式满足条件，但要首先插入子记录，您必须使用父级中的附加列，以便触发器可以了解子表中的其他字段，或者插入空白 （无论哪种情况，设计都是不干净的）
通过安全保障诚信
上述可以作为存储过程来实现，而不需要父表级别上的附加字段。然而，存储过程通常可以被绕过，因此它不符合真正的完整性规则。
有一种通用方法可以使使用存储过程实现的某些内容符合完整性规则，即删除所有常规用户（和应用程序）对这些表的写入权限，并允许仅通过存储过程更改数据。
编辑：
关于触发器，还有一种方法可以用触发器来实现规则，那就是接受您必须单独插入记录，并且您必须在某一时刻拥有破坏业务规则的数据。
在这种情况下，您可以为父记录设置一个 STATUS 属性（例如：COMPLETE 与 INCOMPLETE），并将 favouriteChildId 设置为 NULLable FK，但是当将状态更新为 COMPLETE 时，您可以让触发器检查是否尊重完整性。
这需要额外的列，但可以使事情变得非常干净（您实际上可以在此表上创建一个视图，该视图只会公开完整的记录，有效地使其看起来像带有 FK NOT NULL 的表）。
Out of the business rules that you have given
Your solution of
satisfies 2 and 3.
But also it satisfies the 1 (since favouriteChildId NOT NULL will not allow creating parent records with no children).
Since you already have the above, I will assume that the real question for you is how to make parentId in Child table NOT NULL.
Normally, there are provision in SQL so that you could do something like
in that case 'circular reference' would not be a problem (see DEFERRED)
Mysql does not support it, so you have following options
Triggers:
It can be assumed that at the time of inserting parent record a favorite child is already known then you can have a trigger that would run before insert on the parent table and
NOTE: Problem is that this way you can formally satisfy the criteria, but to insert the child record first you will have to either use additional columns in parent so that the trigger can know about other fields in child table or insert a blank record (in either case the design is not clean)
Integrity through security
The above can be implemented as stored procedure without requiring additional fields on Parent table level. However stored procedures could, normally be bypassed so it does not qualify as real integrity rule.
There is, a generic way to make something achieved with a stored procedure qualify as integrity rule - and that is to remove write permissions for all regular users (and applications) for these tables and allow the data to be changed only through stored procedure.
EDIT:
Regarding triggers there is also a way to implement the rule with triggers, and that is to accept that you will have to insert records separately and that you can must have, at one moment data that breaks your business rules.
In that case you can have a STATUS atrribute for the parent record (for example: COMPLETE vs INCOMPLETE) and make favouriteChildId a NULLable FK, but when updating the status to COMPLETE you can have trigger check that integrity is respected.
This requires additional column but can make things quite clean (you can actually create a view on this table that would only expose only records that are COMPLETE, effectively making it look like the table with FK NOT NULL).
您可以（在一定程度上）对其他约束进行建模：
总是将一行插入“Is favorite Child”中；仅当存在最不喜欢的子项时，才会将其插入到最不喜欢的子项中：由视图上的触发器完成插入、更新、删除；通过表上的左连接进行选择。
这不涉及最喜欢的子关系的强制性质 - 这必须由插入/更新/删除触发器来处理。
You can model (to a degree) the other constraints:
A row would always be inserted into Is Favorite Child; one would be inserted in Is Least Favourite only if there is a least favorite child: Insertion, update, deletion done by a trigger on a view; selection by a Left Join on the tables.
This does not deal with the mandatory nature of the Favourite Child relation - which would have to be dealt with by the insert/update/delete triggers.
如果只有一层深：
每个
Child必须始终有 1 个Parent并且Parents始终有>= 0 个 Children。 （没有办法解决这个问题。）如果有多个（未知）深度：
非常简单：
Items.ParentItemID = Items.ItemID编辑
如果您需要多个（未知）深度的级别，则查询将是多个，并且缓存总结果将是一个非常非常好的主意。 （每个孩子都会有另一个查询来获取它的直接孩子等等。）
If just one level deep:
Every
Childmust always have exactly 1ParentandParentsalways have>= 0 Children. (There's no way around that.)If multiple (unknown) levels deep:
Very simple:
Items.ParentItemID = Items.ItemIDedit
If you require several (unknown) levels deep, the queries will be multiple and caching the total result would be a very, very good idea. (Every child would have another query to fetch it's direct children etc etc.)