# Table sets impossible foreign key for setID **URL:** \ **Category:** Bugs\ **Tags:** odm-v3\ **Created:** [July 23, 2026, 5:00pm UTC](https://odm.discourse.group/t/table-sets-impossible-foreign-key-for-setid/198 "2026-07-23T17:00:04Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![wastewater\_belgium](https://avatars.discourse-cdn.com/v4/letter/w/c0e974/32.png) [@wastewater\_belgium](https://odm.discourse.group/u/wastewater_belgium)\ **Post date:** [July 23, 2026, 5:00pm UTC](https://odm.discourse.group/t/table-sets-impossible-foreign-key-for-setid/198/1 "2026-07-23T17:00:04Z")
Hi, According to my colleague which takes care of our databases there is an issue in the ERD (v3.0.0) concerning the relation between setID in sets table with specimenSet, compartmentSet, mmSet, unitSet, aggregationSet, qualityIndSet, missingessSet in parts table. A foreign key must point at a primary key which contains always unique values. SetID can contain several identical values and thus cannot be a primary key neither a foreign key. A table linking sets and parts could solve this. This tables would contain a variable with all possible setID and could be used as a primary key toward the foreign key setID and toward foreign keys specimenSet, compartmentSet, mmSet, unitSet, aggregationSet, qualityIndSet, missingessSet. Or maybe set them as FK with partID in parts tables. ---
**Author:** ![matthomson](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@matthomson](https://odm.discourse.group/u/matthomson)\ **Post date:** [July 27, 2026, 4:05pm UTC](https://odm.discourse.group/t/table-sets-impossible-foreign-key-for-setid/198/2 "2026-07-27T16:05:42Z")
Hello - thank you for this! The issue is an ERD issue for sure, rather than a structural one in practice (or I believe it is). The problem arrises, I think, because `setID` is a foreign key, but in the ERD in the `sets` table it doesn’t show where that foreign key is coming from. Within the `sets` table, `setCompID` is the PK/CK for the table, while `setID` and `partID` are FKs, both of which come from the `partID` field in the `parts` table. The issue is that `setID` then gets referenced _back_ in the `parts` table under the various `specimenSet`, `compartmentSet`, `mmaSet`, etc. This creates cycling in SQL (self-referential cycle). The logic here though was that because these are reference tables, they aren’t really something that users are going to have to populate or edit locally, and so these kinds of cycling dependencies shouldn’t create a _real_ problem. So we opted to not show the `partID` → `setID` linkage in the ERD to avoid a problem. Alternatively, we could show the `partID` → `setID` linkage and not show the `sets.setID` → `compartmentSet` etc. linkages, and define them instead as “joins”. Which I think could work? I would welcome any thoughts of feedback! More tables are always bit of a headache, so I like to avoid having to add them where possible, but if if it’s not possible then you’re right that a table to better manage these linkages might be the best bet. Let me know! ---
**Author:** ![matthomson](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@matthomson](https://odm.discourse.group/u/matthomson)\ **Post date:** [July 27, 2026, 4:10pm UTC](https://odm.discourse.group/t/table-sets-impossible-foreign-key-for-setid/198/3 "2026-07-27T16:10:28Z")
For some visual representation: This is what we currently have: ![Screenshot 2026-07-27 at 10.08.02 AM](https://global.discourse-cdn.com/free1/uploads/odm/original/1X/c46fd936fdb01ceab6a12357d766ef5c9ac0f84c.jpeg) This would be the option with an implied “join” for the `specimenSet`, `compartmentSet`, `mmaSet` etc. fields but with the `partID` → `setID` key relationship made explicit: ![Screenshot 2026-07-27 at 10.07.46 AM](https://global.discourse-cdn.com/free1/uploads/odm/original/1X/cd8d95b96d3867332a935c4c438d06a75530cb68.jpeg) And this is the “factual” but self-referential cyclical (and thus problematic) representation of the current structure: ![Screenshot 2026-07-27 at 10.07.25 AM](https://global.discourse-cdn.com/free1/uploads/odm/original/1X/23993240854a27779cd5e28bdce788c0b681fa70.jpeg) ---
**Author:** ![wastewater\_belgium](https://avatars.discourse-cdn.com/v4/letter/w/c0e974/32.png) [@wastewater\_belgium](https://odm.discourse.group/u/wastewater_belgium)\ **Post date:** [July 27, 2026, 5:11pm UTC](https://odm.discourse.group/t/table-sets-impossible-foreign-key-for-setid/198/4 "2026-07-27T17:11:24Z")
hello, Thanks for the explanations. I think having setID as foreign key with partID as primary key is the best solution. Regarding the last picture, the blue foreign keys could be linked to the primary key partID. It will be a primary key/foreign key relation within the same table and it ensures that specimenSet, compartmentSet, mmaSet, unitSet, etc. exist as partID. ![image](https://global.discourse-cdn.com/free1/uploads/odm/original/1X/8d7742ccfdfed8079785172d4f1747f78292611c.jpeg) ---
**Author:** ![matthomson](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@matthomson](https://odm.discourse.group/u/matthomson)\ **Post date:** [July 27, 2026, 8:39pm UTC](https://odm.discourse.group/t/table-sets-impossible-foreign-key-for-setid/198/5 "2026-07-27T20:39:05Z")
That’s a good idea! I think the idea was to maintain the continuity to show that sets are all defined in the sets table. But I suppose that can still be clear even without the cycling identities. I’ll make the change to look like this ahead of the 3.0.1 release. Thank you again! ![Screenshot 2026-07-27 at 2.38.36 PM](https://global.discourse-cdn.com/free1/uploads/odm/original/1X/dcc838a4b72b1b9284526bbd921461d8951e2eb4.jpeg) ---
**Author:** ![system](https://global.discourse-cdn.com/free1/uploads/odm/original/1X/34f6410d4262a308493f8c3b977a49666a3a2929.png) [@system](https://odm.discourse.group/u/system)\ **Post date:** [July 28, 2026, 8:39pm UTC](https://odm.discourse.group/t/table-sets-impossible-foreign-key-for-setid/198/6 "2026-07-28T20:39:27Z")
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.