Set of values of a field of a related entity must be unique: How do I express this constraint in SQL? Given the following three tables:

Type( id int primary key, 
      key varchar )

GroupOfInstances( id int primary key, 
                  name varchar )

InstanceOfType( id int primary key, 
                type int foreign key references(Type.id), 
                group int foreign key references(GroupOfInstances.id) )


As seen from GroupOfInstances, I want the set of key values to be unique. I can't make key unique on Type.

To put it another way, I want all keys assigned to a GroupOfInstances through InstanceOfType to be unique for each GroupOfInstances.

How do I do that? I'm using MariaDB 10.1. I can't upgrade to >=10.2 where check constraints are enforced for stupid large enterprise reasons.



Edit: To better illustrate the question I will add examples.

This should be allowed:

INSERT INTO Type (id, key) VALUES (1, "key"), (2, "key"), (3, "otherkey")
INSERT INTO GroupOfInstances (id, name) (1, "group")
INSERT INTO InstanceOfType (id, type, group) VALUES (1, 1, 1), (2, 3, 1)


Because for GroupOfInstances with id 1, they keys are unique: "key" from Type 1 and "otherkey" from Type 3.

This should not be allowed:

INSERT INTO Type (id, key) VALUES (1, "key"), (2, "key"), (3, "otherkey")
INSERT INTO GroupOfInstances (id, name) (1, "group")
INSERT INTO InstanceOfType (id, type, group) VALUES (1, 1, 1), (2, 2, 1)


Because now, for GroupOfInstances 1, the keys are not unique: "key" from Type 1, and "key" again from Type 2. This INSERT should be rejected by the database.
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.