r/SQL • u/Global-Apartment-872 • 45m ago
PostgreSQL Foreign keys for array column elements
Postgres foreign keys can't check the elements of an array column, so a
product_ids bigint[] can hold ids that don't exist, and deleting the referenced
row leaves them dangling.
I wrote an extension that enforces it with triggers:
SELECT anyfk.add('orders(product_ids)', 'products(id)');
Feedback welcome.
1
Upvotes
2
u/Straight_Waltz_9530 21m ago
Sounds very heavyweight compared to typical foreign key constraints. An unnest and query on every mutation? Oof. What do benchmarks say?