How to create conditional single restriction for several tables?

Question

I am working on a web application using Hibernate 4 and Oracle 11g.

I have the following tables that I work with in this scenario. The tables have been modified and simplified to protect the innocent.

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.    |
...

The idea is that I want to conditionally restrict insert_number inserts so that there can only be one unique type number assigned to any given record. Unfortunately, many of the simple restriction approaches do not work for this scenario. After some research, I found the following solutions:

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

, , , id "type_id", , , . Oracle "type.name", , .

, , , - , ? , , ? , ?

Answer 1

, ; - :

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)
;

( : . " " . , , , " ", .)