1

Suppose I have two tables: thread and user, with a join table thread_user that models a many-to-many relationship between them.

Suppose I want to enforce the following constraints:

  • A thread can have at most 10 users.
  • No two threads should have the same set of users.

How can this be done? The first constraint seems easy enough to enforce with a trigger (is there a better approach?). The second constraint I have no idea how to enforce.

Dmitry Minkovsky
  • 36,185
  • 26
  • 116
  • 160
  • Do the sets of users for two different threads have to have empty intersection, or is it enough that the sets are not the same? – Laurenz Albe Feb 06 '20 at 18:57
  • @LaurenzAlbe it's only required that they are not the same set. The intersections don't have to be empty. These are "threads" as in a chat application, so many threads can have overlapping users, but ideally you won't have two threads that have the same set of users. – Dmitry Minkovsky Feb 06 '20 at 18:59

1 Answers1

4

I could imagine a combination of triggers and constraints/indexes. Actually, you could just use triggers, but that requires handling updates, inserts, and deletes.

Instead, you can modify threads to have two additional columns:

  • The number of users
  • An array of the users

You can keep these up-to-date using triggers. Actually, the first isn't really necessary.

Then you can create a check constraint:

alter table t add constraint chk_t_num_users (num_users <= 10);

If you keep the array in sorted order, you can just add a unique index:

create unique index unq_t_users on (users);

Or you can define your own sort_array() function, as in this answer.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you, makes sense. Not the trigger I imagined, because I was thinking there must be a way without denormalizing, but this makes sense. – Dmitry Minkovsky Feb 06 '20 at 19:15
  • @DmitryMinkovsky . . . I would be very careful about the threads not having the same set of users. That seems like it might have unexpected consequences. – Gordon Linoff Feb 06 '20 at 19:17