MongoDB Virtual Foreign Keys Explained

One collection in a MongoDB database holds an id that points into another collection, and the server has nothing to say about it. No constraint declares the link, no delete cascades along it, and a document may carry an id that matches nothing. DbSchema lets you record the link on your side instead: drag one field onto the field it points at, and a connector line joins the two collections on the diagram. That link is a virtual foreign key.

What is a foreign key?

In a relational database, a foreign key is a column, or a group of columns, that cross-references two tables. The parent table holds the referenced column, normally its primary key. The child table holds the column that points at it, and the database refuses any row whose value has no match.

CREATE TABLE country (country_id int PRIMARY KEY, country text NOT NULL);
CREATE TABLE city (
  city_id   int PRIMARY KEY,
  city      text NOT NULL,
  country_id int NOT NULL REFERENCES country
);

Every city row now carries the country it belongs to, so one query returns all the cities of one country. The constraint also decides what happens when the parent row goes. The default refuses the delete while cities still reference the country. ON DELETE CASCADE removes those cities with it, and ON DELETE SET NULL leaves them with an empty country_id. DbSchema exposes the same choices in its Foreign Key Editor.

The constraint lives in the database, so whatever reads the catalog can draw the relationship without being told about it. That is why reverse-engineering a relational schema produces a complete diagram on the first attempt.

MongoDB references

MongoDB has neither the constraint nor the actions. It stores collections, documents and fields rather than tables, rows and columns, and it offers two ways of keeping related data.

Denormalization puts the related data inside the same document. A person document carries the addresses of that person in an array, and one read returns both.

db.person.findOne()

{
  name: "Mark Kornfield",
  ssn: "1223-234-75554",
  addresses: [
    { street: "123 Church St", city: "Miami", cc: "USA" },
    { street: "123 Mary Av", city: "Los Angeles", cc: "USA" }
  ]
}

Normalization keeps the related data in a second collection and stores the identifier instead, which is where references come in. A product lists the ObjectId of each part it is built from.

db.parts.insertMany([
  { _id: ObjectId("000000000000000000000aaa"), partno: "1224-dsdf-2215", name: "bearing", price: 2.63 },
  { _id: ObjectId("000000000000000000000bbb"), partno: "9981-kkfa-1102", name: "rim", price: 12.4 }
])

db.products.insertOne({
  name: "wheel",
  manufacturer: "Ford",
  catalog_number: 2234,
  parts: [ ObjectId("000000000000000000000aaa"), ObjectId("000000000000000000000bbb") ]
})

Reading the parts of one product takes two round trips. Fetch the product by its catalog number, then fetch the documents whose _id appears in the array you just read.

const product = db.products.findOne({ catalog_number: 2234 })
db.parts.find({ _id: { $in: product.parts } }).toArray()

[
  { _id: ObjectId("000000000000000000000aaa"), partno: "1224-dsdf-2215", name: "bearing", price: 2.63 },
  { _id: ObjectId("000000000000000000000bbb"), partno: "9981-kkfa-1102", name: "rim", price: 12.4 }
]

That second query is the application's job. A stored _id of this kind is what the MongoDB manual calls a manual reference, and the manual is explicit that the application runs the extra query to resolve it. The database keeps no record that the two collections belong together, so nothing on the server can draw you the relationship or check it.

Creating virtual foreign keys in MongoDB

DbSchema keeps its own copy of the structure in a design model file, a local XML file. You can work on the model with no connection open, and compare it against the database later from Schema → Compare Model with Database. The model file is also what makes virtual relations possible: it has somewhere to put a link the database will not store.

DbSchema fills that model by connecting to MongoDB and introspecting a configurable sample of documents per collection, inferring the field names, the BSON types, the nested objects and the arrays it finds in the sample. What lands on the diagram approximates what the sampled documents contain, and it is never a structure MongoDB enforces. Where a collection carries a validation rule, DbSchema reverse-engineers that rule instead and treats it as the authoritative structure. Creating or editing a collection in DbSchema writes the validation rule back to the database and to the model file together.

Take two collections that share a field.

db.countries.insertOne({ country_id: 1, country_name: "USA" })

db.cities.insertMany([
  { country_id: 1, city_name: "Miami" },
  { country_id: 1, city_name: "Los Angeles" }
])

Nothing above tells MongoDB that cities.country_id refers to countries.country_id. You tell DbSchema. Hover over the referencing field on the diagram and a connector handle appears on its right edge. Drag from that handle to the field it points at in the other collection, and DbSchema asks whether the foreign key should be real or virtual. Choose virtual and the connector line is drawn straight away.

Double-click the line to open the Foreign Key Editor, where you can add a description or map further fields into the same relation. A virtual foreign key exists only in the model file. MongoDB neither declares nor enforces the link, so no constraint is created in the database and the running application sees exactly the database it saw before. Editing a collection is the operation that changes both sides; drawing a relation changes only the model.

Faster data browsing

A virtual relation is not only a line on a picture. The Relational Data Editor reads those relations and opens several collections side by side in one window. Open it from the Editors menu with New Relational Data Editor, or right-click a collection header on the diagram and choose Open in Relational Data Editor.

Select a country in the parent pane and every child pane refilters to the documents whose field values match, so the cities of that country appear next to it. The cascade goes as many levels deep as the relations do: pick a country, see its cities, and pick a city to see what hangs off that. You write none of the queries, and the joins MongoDB has no syntax for are the ones DbSchema resolves for you from the relations you drew.

Handing the model to someone else

Comments belong on the model too. Double-click a collection or a field on the diagram and enter a description, then export the whole thing from Diagram → Export HTML5 or PDF Documentation. The interactive HTML5 documentation opens in any browser with no server behind it, carries the diagram as a vector image, and shows those collection and field comments as mouse-over tooltips.

Five things make one chain: a structure inferred from the documents themselves, the collection validation rule where one exists, the virtual relations between collections, the browsing that walks across them, and the interactive HTML5 documentation at the end. Other tools cover parts of that chain. The combination is what turns a MongoDB database into something you can hand to a colleague who has never opened it.

Start where the ambiguity is worst. Download DbSchema at https://dbschema.com/download.html, point it at your MongoDB database, and take the one collection nobody on the team can explain: reverse-engineer it, connect its identifier fields to the collections they name, then open the two side by side and read a record with its children. Connecting, reverse-engineering and the interactive diagrams are in the free Community Edition. Saving the model to a file, which is where virtual foreign keys are kept, the Relational Data Editor and the HTML5 documentation are in Pro.