Is there a straightforward way to determine the cardinality of a foreign key relationship using SMO?

Question

Using SMO, I'm trying to determine the cardinality of ForeignKey relationships between tables in a SQL Server database. I could peruse each ForeignKeyColumn and look at each Column in the parent (and possibly also referenced) Tables, and look at the nullability of those columns as well as cross referencing with related Indexes to determine cardinality (multiplicity on each end such as 0..1, 1, *) as illustrated by the following entity-relationship diagram:

But, there should be a more straightforward way to do this. I don't see any relevant information in the ForeignKey Properties or ExtendedProperties. The only item I could find in the MSDN Library was an SfcCardinality enumeration, which states "Do not reference this member directly in your code. It supports the SQL Server infrastructure.".

Is there a straight forward way to get cardinality information via SMO?

Answer 1

I'm currently determining the multiplicity of the foreign key relationship based on related indexes and columns.

Given 2 tables A and B, where B is the referenced table (principal) of a foreign key in A, I am able to determine 3 multiplicity cases using SMO:

One-to-one, where B must exist and A may exist:

A<-0..1----------1->B ()

This case is true if all of the columns of A in the foreign key relationship match a unique index within A.

Many-to-one, where B must exist if A is present, and A may exist:

A<-0..*----------1->B ()

This case is true if case 1 is not true and all of the columns of A in the foreign key relationship are not nullable.

Many-to-one, where B may exist if A is present, and A may exist:

A<-0..*----------0..1->B ()

This case is true if any of the columns of A in the foreign key relationship are nullable.

---

These 3 cases seem similar to what other tools such as Entity Framework can determine, so I'm not sure if it's easy to get additional multiplicity cases such as A<-1----------1->B, A<-*----------1->B, or A<-n----------1->B. You probably would have to peruse constraints and/or triggers to glean additional information.

If there is a way to get more multiplicity cases, and/or a better way to get these cases, I'd love to know!