Declarative integrity constraint between rows without pivot

Question

I have a situation like the following join table:

A_ID  B_ID
1     27
1     314
1     5

I need to put a constraint on the table that will prevent a duplicate group from being entered. In other words:

A_ID  B_ID
2     27
2     314
2     5

should fail, but

A_ID  B_ID
3     27
3     314

should succeed, because it's a distinct group.

The 2 ways I've thought of are:

1. Pivot the table in a materialize view based upon the order and put a unique key on the pivot fields. I don't like this because in Oracle I have to limit the number of rows in a group because of both the pivoting rules, and the 32-column index limitation (thought of a way around this second problem, but still).
2. Create some unique hash value on the combination of the B_IDs and make that unique. Maybe I'm not enough of a mathematician, but I can't think of a way to do this that doesn't limit the number of values that I can use for B_ID.

I feel like there's something obvious I'm missing here, like I could just add some sort of an ordering column and set a different unique key, but I've done quite a bit of reading and haven't come up with anything. It might also be that the data model I inherited is flawed, but I can't think of anything that would give me similar flexibility.

Answer 1

Firstly a regular constraint can't work.

If the set with A_ID of 1 exists, and then session 1 inserts a record with A_ID 2 and B_ID of 27, session 2 inserts (2,314) and session 3 inserts (2,5), then none of those would see a conflict to cause a constraint violation. Triggers won't work either. Equally, if a set existed of (6,99), then it would be difficult for another session to create a new set of (6,99,300).

The MV with 'refresh on commit' could work, preventing the last session from successfully committing. I'd look more at the hashing option, summing up the hashed B_ID's for each A_ID

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

While hash collisions are possible, they are very unlikely.

If you are on 11g check out LISTAGG too.

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