[SERVER-57222] Foreign Key Constraint on field Created: 26/May/21  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Dimitris Halatsis Assignee: Backlog - Query Execution
Resolution: Unresolved Votes: 0
Labels: constraint, foreign, index, key, reference
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Execution
Participants:

 Description   

I have been working with MongoDB for quite some time and I find that with the proper schema design, it can fit most generic use cases.

However there are cases where referencing between collections (SQL like) is unavoidable. In these cases, the biggest drawback with Mongo compared to SQL DBs is  the foreign key constraint inability.

Even checking using application logic, isn't 100% correct, since if you check for the validity of the field value before inserting the document, you can't be sure that it wasn't deleted when you create it and you have to check after creation and apply compensating deletion - error handling.

I believe an index functionality where it would assert the existence of the field's value in an other's collection index  would be extremely helpful and remove this major drawback of Mongo compared to sequential databases,  since the check in the database level would allow for proper foreign key constraint.

Tried to find similar issues - feature requests but didn't find any, that's why I created this one.

Any thoughs on that?

 



 Comments   
Comment by Dimitris Halatsis [ 22/Jun/21 ]

Thanks for the update!

Looking forward to the team's feedback

Comment by Kyle Suarez [ 22/Jun/21 ]

Thanks for the feature request, dimitris_ha@hotmail.com. We are adding this to the Query Team project backlog for consideration.

Comment by Dimitris Halatsis [ 17/Jun/21 ]

Hi @EricSedor and thanks for replying to my issue. I appreciate taking it over to the appropriate team for consideration. I believe it is something worth working on.

Regarding your suggestion, wouldn't it be more intuitive if the options you mentioned (namespace - collection, indexed field), were defined in the index options? Since they regard the index definition and for it to work properly, it can only rely on index existence?

Regards

Comment by Eric Sedor [ 16/Jun/21 ]

Hi dimitris_ha@hotmail.com,

I'll pass this to an appropriate team to consider. One initial thought I have is that one way to provide this feature might be a syntax for referencing a namespace and indexed field within document validators.

Eric

Generated at Thu Feb 08 05:41:17 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.