0

Foreign Key constraints

Eric Holterman

August 31, 2019 19:06

None

Follow

Hello - I have 2 questions. 

First -  it's great that Hackolade has support for documenting Foreign Keys and Foreign Masters.  I've found the Excel report that documents relationships, but are there any other "forward engineering" tools for managing foreign keys and foreign masters in a MongodB / JSONSchema way?

Second - I'm not seeing any way to identify fields as having a "unique" requirement.  I would like to be able to do this in Hackolade and then produce a .js for Mongoose that includes the "unique" constraint.  Please advise if this is possible or on the roadmap.  

Thank you!

• Facebook
• Twitter
• LinkedIn

4 comments

• 
  Helpdesk August 31, 2019 19:16 Official comment

  Hello Eric,

  I need some clarifications:

  1) what do you mean with "managing foreign keys and foreign masters in a MongodB / JSONSchema way?"

  2) We have tried to include, for each data type of each target, the properties that seemed most useful for most users.  If there's one missing, you may add it, using https://hackolade.com/help/Userdefinedcustomproperties.html  Is that what you're looking for?

  Pascal

• 0
  
  Helpdesk August 31, 2019 19:18

  One more thing for 2), Unique is a property of MongoDB indexes and is already supported there.

  Pascal

• 0
  
  Eric Holterman August 31, 2019 19:43

  Pascal - I do see that "unique" is supported for MongoDB indexes, and we do create unique indexes for fields that must be unique, but we also try to do all validation in Mongoose, so it looks like what we need to do for now is add the "unique" constraint to the Mongoose .js files by hand based on what we've configured for unique index keys in MongoDb.

  Regarding the first question:  I haven't really come up with a way to manage foreign key constraints in MongoDB better than what is described here:

  http://egorpanok.com/how-to-emulate-foreign-key-constraints-with-mongoose/

  Since I'm so new to Hackolade (and JSONschema standard) I was hoping there was a way to create relationships in Hackolade and then generate mongoose or MongoDB validation that would automatically enforce the constraint on inserts/updates to the child collection (i.e. don't allow the insert/update if the foreign key isn't present in the parent collection).

  The other part of this topic is documentation.  I'd like to be able to produce documentation for foreign keys and masters that describes the relationship using actual "names" of collections and fields vs the UUIDs that are in the Excel report.  Right now the only way I'm seeing to produce that "human readable" documentation is to create the forward-engineering Excel report that includes "relationships" and then write a script to map the UUIDs on "relationships" tab of the report to the actual collection names and field names. The documentation I'm looking for would look something like what I've shown below but would have human-readable collection names and field names instead of UUIDs

  "relationships": [
  {
  "GUID": "05f92170-899e-11e6-a8d7-35e2bcca9f8c",
  "relationshipType": "Foreign Key",
  "parentField": [
  "05fa0bd2-899e-11e6-a8d7-35e2bcca9f8c",
  "05fa0bd3-899e-11e6-a8d7-35e2bcca9f8c"
  ],
  "parentCardinality": "1",
  "childField": [
  "05fa0bd2-899e-11e6-a8d7-35e2bcca9f8c",
  "05fa59f3-899e-11e6-a8d7-35e2bcca9f8c"
  ],
  "childCardinality": "0..n",
  "name": "fk_reports_to",
  "description": "",
  "comments": "",
  "parentCollection": "05fa0bd2-899e-11e6-a8d7-35e2bcca9f8c",
  "childCollection": "05fa0bd2-899e-11e6-a8d7-35e2bcca9f8c"
  },

   

  Thank you for being so responsive!  -Eric

• 0
  
  Helpdesk August 31, 2019 20:19

  Hi Eric,

  2) regarding the extra property, you may define your own, including Unique, by following the link provided earlier.

  1) one of the ways NoSQL achieves its goals is by not having foreign key constraints enforced at the server level.  MongoDB does have this available: https://docs.mongodb.com/manual/reference/database-references/ which Hackolade supports.  But be careful about its caveats, cfr https://www.compose.com/articles/mongodb-and-the-trouble-with-dbrefs/

  For documentation, keep in mind the HTML documentation that Hackolade provides, including relationships defined in Hackolade (but which cannot be forward-engineered to the DB) and DBRefs if present.

  Pascal