0

I am stuck in a problem where i have to find cardinality of a relationship between tables using mysql. Following this post
MySQL: How to determine foreign key relationships programmatically?
I have found all tables related to my table and the columns which are foreign key. Now i also want to find the cardinality of relationship i.e. one-to-one, one-to-many or many-to-many. Any ideas or snippets would be highly appreciated

Community
  • 1
  • 1
Code Prank
  • 4,049
  • 5
  • 29
  • 45
  • Do you really need to calculate current cardinality using related tables? Cardinality is not about possible values it's about current data. So, query should be: `count(distinct values in column)/count(rows in table)` – ravnur Sep 27 '12 at 09:49
  • i just want to find whether the relationship is one-to-one, one-to-many or many-to-many between the tables – Code Prank Sep 27 '12 at 10:09
  • If you do not find dups in child table that doesn't means relation is 1:1. The same with 1:N, M:N. In other words you can't be sure about relation type relying only on finding dups in child/parent tables. You can only find M:N relation and it would be correct. Other answers would be like "may be it's 1:1 or 1:N" – ravnur Sep 27 '12 at 10:23
  • yeah but i think there must be some way to find out that. If you have used yii framework its crud generator buids relationship type like HAS_MANY, MANY_MANY, HAS_ONE etc. means there is some way to find out. – Code Prank Sep 27 '12 at 10:57
  • Sorry, i do not familiar with this framework. Here is pseudo code which can maybe helps you to find relation type based on data. But be aware of escalating relation type. Here is the link: https://gist.github.com/3793455. Just a guidance – ravnur Sep 27 '12 at 11:03
  • yeah i got how he had find out but this would not be helpful in case if somebody have a fresh database mean there is no data then it wont work. – Code Prank Sep 27 '12 at 11:31

1 Answers1

6

Let us assume that table A has a foreign key f which refers to the primary key k of table B. Then you can learn the following from the schema:

  • If there is a UNIQUE constraint on A.f, then there can be at most one row in A for every row in B. Note that in the case of multi-column indices, all columns of the unique constraint must be part of the foreign key. You can use SHOW INDEX FROM tablename WHERE Non_unique = 0 to obtain information on the uniqueness constraints of a table.
  • If A.f is declared NOT NULL, then there will always be at least one row in B for every row in A. You can use SHOW COLUMNS FROM tablename to list the columns and see which of them allow NULL values.

If you interpret “one” as “zero or one”, then you get a one-to-one relation using a unique constraint, and a many-to-one relation (i.e. many rows in A referring to one row in B) without such a unique constraint.

A many-to-many relation would be modeled using a separate table, where each row represents one element of the relation, with many-to-one relations for both foreign keys it contains.

MvG
  • 51,562
  • 13
  • 126
  • 251