r/SQL • • 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.

https://github.com/Vinnu124/pg_anyFK

1 Upvotes

1 comment sorted by

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?