A foreign key constraint in postgres requires that the entire column value in the foreign key match a corresponding column value of a key in the referenced domain table. Unless there have been quite recent extensions to the DDL in postgres, there is no foreign key constraint syntax to express a REFERENCES constraint over parts of a structured column. This is not supported for even the simple case of constraining one field in a UDT/composite type, much less the harder problem of constraining a variable cardinality set of elements in an array, or keys/values within a json/jsonb document.
You can try to emulate it with triggers, and start to appreciate the subtle complexities of the problem. For example, would we want a new class of element-wise actions to act analogous to ON DELETE/UPDATE CASCADE/SET NULL? Rather than pruning referenced row or referencing column value, you'd want to mutate just an element within the structured type, right? How would you expose these many choices if trying to design a new constraint syntax with reusable machinery?
You could also use the array to store only the order, and regular FK relationships for membership. That way the referential integrity of the items is guaranteed, but the order is more of a "best effort" approach, as in that there my be members that have no defined order (default to putting them last or first), or there may be items in the order array that have been deleted (and can then be ignored)
Comments
Is there a way in PG to keep the foreign key constraints on an array of ids, or do you just have to give them up for practical reasons?
A foreign key constraint in postgres requires that the entire column value in the foreign key match a corresponding column value of a key in the referenced domain table. Unless there have been quite recent extensions to the DDL in postgres, there is no foreign key constraint syntax to express a REFERENCES constraint over parts of a structured column. This is not supported for even the simple case of constraining one field in a UDT/composite type, much less the harder problem of constraining a variable cardinality set of elements in an array, or keys/values within a json/jsonb document.
You can try to emulate it with triggers, and start to appreciate the subtle complexities of the problem. For example, would we want a new class of element-wise actions to act analogous to ON DELETE/UPDATE CASCADE/SET NULL? Rather than pruning referenced row or referencing column value, you'd want to mutate just an element within the structured type, right? How would you expose these many choices if trying to design a new constraint syntax with reusable machinery?
You could also use the array to store only the order, and regular FK relationships for membership. That way the referential integrity of the items is guaranteed, but the order is more of a "best effort" approach, as in that there my be members that have no defined order (default to putting them last or first), or there may be items in the order array that have been deleted (and can then be ignored)
As far as I know the FKs dont work with arrays but you can emulate them via triggers.