没有主元的行之间的声明性完整性约束

Question

我遇到类似于以下连接表的情况：

A_ID  B_ID
1     27
1     314
1     5

我需要在表上施加约束，以防止输入重复的组。换句话说：

A_ID  B_ID
2     27
2     314
2     5

应该失败，但

A_ID  B_ID
3     27
3     314

应该成功，因为它是一个独特的群体。

我想到的两种方法是：

1. 根据顺序在物化视图中透视表，并在透视字段上放置唯一的键。我不喜欢这个，因为在 Oracle 中，由于旋转规则和 32 列索引限制，我必须限制组中的行数（想到了解决第二个问题的方法，但仍然如此）。
2. 在 B_ID 的组合上创建一些唯一的哈希值并使其唯一。也许我还不够数学家，但我想不出一种方法来做到这一点，而不限制我可以用于 B_ID 的值的数量。

我觉得这里明显缺少一些东西，就像我可以添加某种排序列并设置不同的唯一键，但我已经做了相当多的阅读，但还没有想出任何东西。也可能是我继承的数据模型有缺陷，但我想不出任何能给我类似灵活性的东西。

Answer 1

首先，常规约束不起作用。

如果 A_ID 为 1 的集合存在，然后会话 1 插入一条 A_ID 为 2、B_ID 为 27 的记录，会话 2 插入 (2,314)，会话 3 插入 (2,5)，那么这些都不会导致冲突违反约束。触发器也不起作用。同样，如果存在 (6,99) 的集合，则另一个会话将很难创建新的 (6,99,300) 集合。

具有“提交刷新”功能的 MV 可以正常工作，从而阻止最后一个会话成功提交。我会更多地关注哈希选项，总结每个 A_ID 的哈希 B_ID

select table_name, sum(ora_hash(column_id)), count(*)
from user_tab_columns
group by table_name

虽然哈希冲突是可能的，但可能性很小。

如果您使用 11g，也请查看 LISTAGG。

select table_name, listagg(column_id||':') within group (order by column_id) 
from user_tab_columns
group by table_name