Referential integrity

[harisvsulaiman]

Is there a way to enforce referential integrity in mongodb?

@vkarpov15

[Uzlopak]

If you need to ensure referential integrity then mongodb or any nosql dbms are not the right choice. You can not enforce it on the server side, like you could do it with SQL and constraints. You have to do everything on client side.

[harisvsulaiman]

Is there any way to mimic referential integrity in mongodb . I guess you could write a plugingto references when documents are deleted

[Uzlopak]

As i already said: server-side: no.

You have to maintain everything yourself.

Referential integrity is usually necessary for hierarchical data. But nosql databases work best for non-hierachical data, were it is ok when searching for documents does not need to be very precise, like it is ok to know that you have about 1 mio entries and dont need to know that you have exact 1004563 entries. Like google search, or like big databases filled with logs like server logs or tracking data like google Analytics etc. etc..

E.g. in sql you would create tables with foreign keys referencing to other tables and setting constrains on delete, so that when you delete the main entry also all connected referenced entries in other databases get deleted too. This means also using normalized database tables as best practice to reduce performance degredation because insterting data with constraints is slower than just dumping entries into a database table. So usually you would have one foreign key constraint per table.

But mongodb works faster when you not normalize data and treat every document as non-hierachical data. Instead of searching over joined tables you should use collections which have probably more information than actually needed to avoid lookups and graph lookUps. Also when you use sharding lookup and graphlookup doesnt work anymore reliable.

So to implement something like referencial integrity you have to keep yourself track of the cross references. And if you delete a document you have to go through all collections and remove them too.

To ensure that this works properly you need to run you mongodb as replicaset to have the ability to use multi document transactions.

Also it affects your documents design.

Imagine you have the hierarchical entities organization, project and resource.

While in sql you have usually one foreign key constraint per table and expect that you store the organizationId in the project and projectId in the resource and if you delete the organization it deletes all projects and because resource has a foreign key constraint on the deleted projectIds, it also deletes the corresponding resources.

In mongo/nosql you would also store the organizationId in the resource. Because if you delete the organization, you have to delete the projects and resources manually. And to avoid roundtrips, like delete organisation, delete projects and use the ids of the deleted projects to delete the resources, you would delete the organization by organizationId and use the organizationId to delete all projects having the organizationId and all resources having the organizationId.

On the other hand that can become also very slow. That opens now the can of worms regarding indizes. While you usually have in sql also one index on the foreignKey in mongo, like all dbms, indizes slow down the insert operation. So you probably start also to create an index per foreignKey in your collection. So the more hierarchical your data becomes the more indizes you would use to ensure that your delete is performant. But mongodb indizes are faster when the cardinality of the field is high. Cardinality is the amount of how many distinct values a field in a collection/table has. In our example the cardinality of the organizationId in organization is high but in resource is very low. So you have to measure your performance. If you delete very often organizations, than adding an index on organizationId on resource level makes more sense. If not, then not creating an index on resources keeps the insert-performance high.

But on the other hand if you want to use transactions, than you have to keep in mind that locking documents comes also with a cost. And there is imho a limit on the lifetime of a transaction-session. Imagine you start deleting an organization. If you have no index on the organizationId in resource it has to do a COLLSCAN, and this can be so slow that your transaction-sessions expires. In that case you would probably use sharding, which would be the same as creating a collection per organization.

Also adding an additional hierarchy level on top means adding that hierarchical level on every sublevel.

And so on and so on.

In conclusion:

Is it possible? yes.
Is it maintainable? yes.
Did i see that in production? Yes.

The organization, project, resource hierarchy is from a real world experience i had.

And like any DBMS it needs regular performance checks and analysis if the indizes are right or if the server itself is fast enough. Like dont run mongodb on a raspberry pi connected to a very slow HDD and expect high performance.

But be aware of the impact on the design of your documents. Invest time into the design of the shape of your documents to avoid adding top hierarchies later when you are in production.

And this brings us back to the beginning:
If you have to invest more time into your schema (as mongodb is actually a nosql, this means schema-less (!) Dbms) and have to reimplement stuff that sql databases can handle out of the box, than maybe a sql database makes more sense.

But if you are forced to use mongodb or you definetly want to do use mongodb, than it is also possible, like i described very detailed above.

[Uzlopak]

Also you could do stuff like one database on one mongoserver per organization. If you never want to transfer a project from one organization to another organization, than using different databases is also a reliable solution. So instead of potentially deleting entries millions of entries you would just delete the database. This would mean that you need to do the database switching all the time, which also comes with a cost.

Yeah, maybe it is also overkill, what I described. :D

[vkarpov15]

@Uzlopak is right, no way to enforce referential integrity in the same way that you do in SQL dbs.

There are a couple of patterns we suggest as an alternative:

1. Don't delete documents. Use soft deletes instead. Since a document's _id cannot change, this means the only way you can have a referential integrity issue is when you create a new document. Not entirely bulletproof, but significantly reduces the risk of problems.
2. Embed important data in the document, rather than relying on references. "Sometimes data being out of date is a feature, not a bug".