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

Question

我正在使用 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 的插入，以便只能为任何给定条目分配一个“唯一”类型的数字。不幸的是，许多直接的约束方法不适用于这种情况。经过一番研究，我发现以下解决方案有效:

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”，我可以依靠它来保持一致。

是否有我不知道的不同方法应该使用，或者是否有关于如何缓解此问题的任何建议？最好是在代码更改或数据模型更改方面尽可能不受干扰的？或者这只是我必须学会处理的现实？

Answer 1

事实证明，您可以对物化 View 设置检查约束；所以，你应该能够写出这样的东西:

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" 中的示例。如果您决定走这条路，您需要先通读文档并了解所有内容；特别是，我不是很清楚在“物化 View 日志”部分，它可能需要一些调整。)