建模一对一关系 [英] Modeling One-to-Constant Relationship

问题描述

一个一对一关系可以在Oracle中完全建模，并且有约束？换句话说，PARENT实体总是具有子实体的非常n-CHILDREN，并且每个子节点只有一个父节点。

考虑n作为数据库常量。 / p>

解决方案

这样做，即使多个会话正在进行更新也是很正确的，并不容易。如果您尝试使用触发器，那么您将会遇到困扰，Oracle的声明限制不够强大，无法表达这一点。

可以这样做： -

1. 在父表和子表上创建物化视图日志


2. 创建物化连接视图将它们合并在一起，并计算由父母分组的孩子的数量。这必须是REFRESH FAST ON COMMIT


3. 在物化连接视图上约束子记录的计数必须等于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:-

1. Create a materialized view log on both the parent and the child tables
2. 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
3. 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屋！