fix: composite foreign key should fail if part of foreign key is null. closes #3876

[mswertz]

closes #3876 see unit test and example in bug report

changes:

• add a null check because postgresql enforces foreign keys only if all elements are not null
• fixes to data model => 'variables.collection event' should be overlapping.
• fixed example data, rename 'collection event.name' to 'collection.event', delete 'collection event.resource'

As alternative I considered a trigger. This would make it more robust but also would require migrations. Therefore opted to solve this in our middleware.

todo:

• [x] make it also work sensible for composite keys that are overlapping, e.g. 'resource,subcohorts'
• [x] check that overlapping works for original variables issue

[sonarcloud[bot]]

Quality Gate passed

Issues
3 New issues
0 Accepted issues

Measures
0 Security Hotspots
100.0% Coverage on New Code
0.0% Duplication on New Code

See analysis details on SonarCloud