oracle 如何跨多个表创建条件唯一约束？

vpfxa7rd 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(153)

我正在使用Hibernate 4和Oracle 11g开发Web应用程序。
我在这个场景中使用了以下表格。为了保护无辜的人，表格已经被修改和简化。

entry
ID  |   name    |
1   |   thing1  |
2   |   thing2  |

entry_number
ID  |   value   |   entry_id|   type_id |
1   |   11111   |   1       |   1       |
2   |   22222   |   1       |   2       |
3   |   33333   |   1       |   2       |
4   |   aaaaa   |   2       |   1       |
5   |   bbbbb   |   2       |   2       |
6   |   ccccc   |   2       |   2       |

type
ID  |   name    |
1   |   unique  |
2   |   regular |
3   |   etc.    |
...

这个想法是，我想有条件地限制entry_number的插入，以便只能有一个类型为“unique”的数字分配给任何给定的条目。不幸的是，许多直接的约束方法都不适用于这种情况。经过研究，我发现以下解决方案有效：

create unique index unique_entry_number on entry_number(CASE WHEN TYPE_ID = 1 THEN entry_id ELSE null END);

我唯一不喜欢的是我引用了“type_id”的id，我不相信我可以依赖它来保持一致。Oracle不允许我在唯一索引中使用子查询来连接“type.name“，我可以依赖它来保持一致。
有没有我不知道的其他方法，或者有没有关于如何缓解这个问题的建议？最好是在代码更改或数据模型更改方面尽可能无干扰的？或者这只是一个我必须学会处理的现实？

oracle

来源：https://stackoverflow.com/questions/22361991/how-do-i-create-a-conditional-unique-constraint-across-multiple-tables

2条答案

按热度按时间

fkaflof61#

事实证明，您可以在实体化视图上设置检查约束;所以，你应该能够写这样的东西：

CREATE MATERIALIZED VIEW LOG
    ON entry_number
    WITH ROWID
;
CREATE MATERIALIZED VIEW LOG
    ON type
    WITH ROWID
;
CREATE MATERIALIZED VIEW entry_number_counter
    REFRESH FAST
    ON COMMIT
    AS SELECT en.entry_id, COUNT(1) AS row_count
           FROM entry_number en
           JOIN type ON entry_number.type_id = type.id
           WHERE type.name = 'unique'
           GROUP BY en.entry_id, type.name
;
ALTER TABLE entry_number_counter
    ADD CONSTRAINT entry_id_conditionally_unique
       CHECK (row_count = 1)
;

（免责声明：未测试。这是根据Tom Kyte's "The Trouble With Triggers"中的一个例子改编的。如果您决定走这条路，您需要先通读文档并理解所有内容;特别是，我对“物化视图日志”部分不是很清楚，它可能需要一些调整。）

赞(0）回复(0）举报 2023-10-16

ndh0cuux2#

您可以在Oracle中使用复合触发器来确保多个表之间的唯一性。这篇文章描述了如何在DML级别实现检查。https://rammpeter.blogspot.com/2023/08/oracle-db-ensure-uniqueness-across.html?m=1

赞(0）回复(0）举报 2023-10-16