Referential integrity for database arrays
A classic referential integrity model in relational databases is based on
FOREIGN KEYs, implemented as
REFERENCES in the PostgreSQL DB. This feature has several advantages, preventing irrecoverable errors in data and allowing
CASCADE of data modification procedures.
The database system used currently does not support references for array values, which are used commonly in the Mentat database model and missing this feature caused several inconsistencies (albeit with minimum impact so far).
A workaround at the database level might be a using a
TRIGGER, checking the data during all modifications. There are two paths that must be analyzed:
UPDATE to the referring array and
DELETE to the
KEY column/table. Both should be doable using the
Things to consider:
- implementation feasibility,
- performance impact in all scenarios (read should not be affected, others must be analyzed),
- parametrisability/reusability (there are several such reference relations, with more to come).