Can I validate fields in one collection against values in another?

I would like to make sure that db.CollectionX.fieldY is constrained to values in db.CollectionY. Essentially this is a schema enum, but the enum has to be drawn from another collection, not typed directly in the schema. (This would violate DRY in our application, since we also need these values elsewhere in the code.)

What will happen if the values in db.CollectionY get deleted?

If the answer is along the lines of nothing should happen to db.CollectionX or the values in db.CollectionY don’t ever get deleted, the solution is to validate the expected value in db.CollectionY in the application layer (probably in controllers if the project structure mentioning has one) before writing to db.CollectionX.

raise NoEnum unless CollectionWhy.where({ value: value_to_test }).exists?

That is what I would do, given only the info you mentioned.

We ALREADY validate the values in the application layer. The issue is, let’s say someone enters ‘Otters’ (in collections ‘Animals’) as ‘Reptiles’ (from collection ‘Classes’). Now we’d like someone using mongosh to be able to correct that to ‘Mammals’. They go to do that, but they mistype the Class as ‘Mamals’. (Humans will do such things!) It would be nice if the DB itself could block such a typo, since ‘Mamals’ is not a valid member of ‘Classes’.

My message is packed in my question. You can’t just take the second half and ignore the first half.

If the validations you are looking for do exist, working the way you wanted it to be, what should happen in the scenario described?

Different situations call for different opinions; it is hard to satisfy everyone. Some want it the foreign-key-constrain way, some don’t. The foreign-key way has some more issues of its own. (The ones who don’t include me.)

Like a lot of things in MongoDB, the database puts the decision power in the hands of applications. Which, IMO, is a way better design than RDBMS.

I don’t see your whole situation, but for me, if my application doesn’t support the correction without proper validations, it is incomplete —no mongosh editing production data.
However, I understand that your situation can be different. I just happened to use the language that solved what you mentioned. It is not really my accomplishment, but people in the community.

Just to be clear, I use Ruby. In production, we REPL on the actual models with validations intact. We even wrote troubleshooting scripts for common scenarios and will add UI if the issue is frequent.

I don’t know every nook and cranny of MongoDB, but I ran production applications with millions of documents and proper business logic for years. I am not aware of such functionality of MongoDB; it might exist, but I don’t know and never heard of it. They provide building blocks to add such functionality when the situation calls for.

I really just needed to know if this could be done. I guess the answer is “no.” But I do appreciate the additional essay on software engineering that accompanied the answer.