建模一对一关系 [英] Modeling One-to-Constant Relationship
问题描述
考虑n作为数据库常量。 / p>
这样做,即使多个会话正在进行更新也是很正确的,并不容易。如果您尝试使用触发器,那么您将会遇到困扰,Oracle的声明限制不够强大,无法表达这一点。
可以这样做: -
- 在父表和子表上创建物化视图日志
- 创建物化连接视图将它们合并在一起,并计算由父母分组的孩子的数量。这必须是REFRESH FAST ON COMMIT
- 在物化连接视图上约束子记录的计数必须等于n(数据库常量)
然后,您可以执行一系列插入/更新/删除语句。当您提交时,物化视图将刷新,如果条件不符合,您将在此时收到约束违规错误。
包括将约束导入实例化视图(HAVING计数(ChildId)5))的行,因此您不会浪费任何存储空间。
Can a One-To-Constant Relationship be completely modeled in Oracle with constraints? In other words, the PARENT entity ALWAYS has EXACTLY n-CHILDREN of the child entity, and each child only has one parent.
Consider n to be a database constant.
Doing this so that it is sound and correct even when multiple sessions are doing updates is not easy. You will get yourself in a mess if you try this with triggers, and Oracle's declarative constraints are not powerful enough to express this.
It can be done as follows:-
- Create a materialized view log on both the parent and the child tables
- Create a materialized join view that joins them together and counts the number of children grouped by the parent. This must be REFRESH FAST ON COMMIT
- Put a constraint on the materialized join view that the count of child records must equal "n" (your database constant)
You can then do a series of insert/update/delete statements. When you commit, the materialized view will refresh and if the condition is not met you will get a constraint violation error at that point.
A bonus bit of trickery is to only include rows that fail the constraint into the materialized view (HAVING count(ChildId) <> 5) so you do not waste any storage space.
这篇关于建模一对一关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!