Array of Integers Referencing Another Table
Hi there,
I'm working with Drizzle ORM and PostgreSQL, and I need to define a column in a table that stores an array of integers. Each integer in the array should reference the id column of an asset table.
Here's what I'm trying to achieve:
The column should store multiple ids from the asset table.
Ideally, I'd like to enforce relational integrity to ensure all the IDs in the array exist in the asset table.
I understand that a junction table is typically recommended for this kind of relationship, but I'm curious if there's a way to define such an array in Drizzle while still referencing another table.
Here are my questions:
Can I define an array of integers in Drizzle that references another table's id?
If not, is a junction table the only way to achieve this in Drizzle while maintaining relational integrity?
Any advice, examples, or best practices would be greatly appreciated. Thanks in advance!
1 Reply
Yes you technically could define an array of integers that reference another table but neither Postgres nor Drizzle could enforce that constraint. You have to maintain the integrity yourself which sounds terrible.
Yes. Definitely, a junction table is the best option.