How to determine cardinality of foreign key using

Question

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

Answer 1

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.