Detect the cardinality of unique foreign key relationships

[aaditmshah-commversion]

1. many to 1 is a non-nullable foreign key.
2. many to [0..1] is a nullable foreign key.
3. [0..1] to 1 is a unique non-nullable foreign key.
4. [0..1] to [0..1] is a unique nullable foreign key.

The cardinality of ref: > relationships should be inferred from the unique, null, and not null constraints.

Table posts {
  // A user may create many posts. A post has only one creator.
  created_by integer [ref: > users.id, not null] // many to 1

  // A user may update many posts. A post might not be updated.
  last_updated_by integer [ref: > users.id, null] // many to [0..1]
}

Table compliance_training_completions {
  // An employee might not have completed the compliance training.
  // A completion record is specific to one employee.
  created_by integer [ref: > employees.id, unique, not null] // [0..1] to 1
}

Table departments {
  // A department may or may not have a manager.
  // An employee can manage at most one department.
  manager_id integer [ref: > employees.id, unique, null] // [0..1] to [0..1]
}

Correct me if I'm wrong. Currently, there's no way to specify the [0..1] to 1 and [0..1] to [0..1] relationships in DBML.

[hdnax]

Hi @aaditmshah-commversion,

In DBML:

• To specify [0..1] to 1 relationships, you can use the - type of ref, like this:

It will show up as a [0..1] to 1 as in the picture.

• The [0..1] to [0..1] relationship is not supported yet though.

Hope this answers your question.

[aaditmshah-commversion]

@hdnax It doesn't work when a.id is declared as a primary key.

Also, in your example the relation should be many to [0..1] because b.id is nullable and not unique. So, a row in B could be related to [0..1] rows in A. However, a row in A could be related to many rows in B.

I'm operating under the assumption that the columns that the foreign key references must be unique. However, here we're creating a reference to a non-unique column. That shouldn't be allowed. And, if it's allowed then the relation becomes many to many.