Optional relationship on ERD diagram

by Michael Sorens   Last Updated October 19, 2019 19:05 PM - source

Upon generating an entity-relationship diagram of my database I observed several occurrence like this:

enter image description here

This diagram (in IDEF1X notation) shows the following:

  1. Our team's DB convention is that each primary table (i.e., not an association-type table) has its own unique primary key called db_id.
  2. iam_projects.db_id is a foreign key in iam_project_rules (the project_id field), hence the connecting line.
  3. iam_projects.db_id is not part of the primary key of iam_project_rules (hence the dotted line style).
  4. A giveniam_projects.db_id may appear zero or more times in iam_project_rules (hence the solid circle on the right end of the connecting line).

Now, here's what my question is about. As I understand the notation, the diamond on the left end of the connecting line changes the left side from "one" to "zero or one". In words, that posits this claim to be true:

There may exist a row in iam_project_rules whose project_id does not appear in iam_projects.

But if iam_project_rules.project_id is a foreign key to iam_projects, which it is, then that claim must be false.

Where is the error in my analysis or assumptions?

Related Questions