Enforce uniqueness across multiple rows I'm trying to enforce groups of activities do not have duplicates without using triggers or materialized views if possible.

DDL to create table:

CREATE TABLE groups (group_id NUMBER, activity_id number) ; 
INSERT INTO groups (group_id, activity_id) values (1,1) ;
INSERT INTO groups (group_id, activity_id) values (1,2) ; 
INSERT INTO groups (group_id, activity_id) values (2,2) ; 
INSERT INTO groups (group_id, activity_id) values (2,3) ; 


The next query should return no duplicates if all groups are made of unique activities. I'd like to enforce that this query never returns any duplicates:

SELECT LISTAGG(ACTIVITY_ID, '|') WITHIN GROUP(ORDER BY ACTIVITY_ID) FROM GROUPS GROUP BY GROUP_ID ; 


My current solution is to insert the result of the LISTAGG query into a table with a unique constraint and use that to control duplicates with my application code handling any constraint violations by searching for the existing row (and thereby getting the group ID to reuse the existing one)
The most frequent issues with these solutions concern concurrency.
For example:


You have group id 'A' with activity 1, and group id 'B' with activity 2.
Ali inserts activity 1 into group 'B' but doesn't commit
Bernie inserts activity 2 into group 'A' but doesn't commit
As far as both Ali and Bernie are concerned, there is no conflict as they cannot see the other individual's change
If your application tries to do a check then commit, you have a race condition where bad data can be recorded between the check and commit
If your application does a commit and then check, you have the possibility of the committed data being left in a bad state if no-one responds to the check.


Depending on how important the check is, and how much concurrency you need to accommodate, the simplest solution is to lock the entire table at the start of the transaction (ie Bernie can't do an insert until Ali does a commit).