Set of values of a field of a related entity must be unique: How do I express this constraint in SQL?
LAST UPDATE:


  I want to allow duplicate key in the table Type, but I want unique key for each GroupOfInstances – Patrick 21 mins ago


The answer do not match the question. Sorry...



Imagine that you have created the constraint which you need.

Imagine that you have a group with keys id=1 and id=2, and you want to create another group with keys id=1, id=2 and id=3.

Formally it is correct - in the final state the groups will have different keysets. But in dynamic...

You try to execute 

INSERT INTO groups_to_keys (group, key) VALUES (2,1), (2,2), (2,3)


You hope that all records will be inserted... but the constraint is checked for each separate inserted record! So 1st value pair will be inserted sucessfully, whereas inserting 2nd pair will be rejected - at this moment the group 2 will fully duplicate the keyset for group 1!

But if you'll try to execute

INSERT INTO groups_to_keys (group, key) VALUES (2,1), (2,3), (2,2)


then all records will be inserted successfully.



The only simple solution I see is de-normalization. I.e. the keys are stored not in separate joining table but as an attribute of group entity in some arrayed form (for example, JSON). Such method allows to create UNIQUE constraint over this field (common UNIQUE index).

Of course this method do not allow to use the FK integrity check.



One more solution - separate table which stores unique keysets, and group is referenced to it. Looks like previous, but this allows to create predefined keysets, including those which are not assigned to any group yet. This table may be service one and available to users for reading only. Or even the user may have privileges to read and to insert, but not alter or delete.