I am working on a project where I have to analyze a MySQL database. I can find every relationship between tables in information_schema.tables, but I need to get the cardinality from each relationship.

In the relational model, tables can be related as any of "one-to-many" or "many-to-many". This is said to be the cardinality of a given table in relation to another.

Is there a way to get that information?

  • 2
    What is relationship cardinality ? Please explain Commented Mar 12, 2014 at 10:13
  • It's relationship between two tables. One to many, many to many like that. Commented Mar 14, 2014 at 7:23
  • Have you tried below solution ? Commented Mar 14, 2014 at 7:25
  • @AbdulManaf Yes I tried, It only gives reference table and refereed table. I want cardinality which we show in ER diagram. Commented Mar 14, 2014 at 12:38
  • 1
    In other DB I will check if the fields of the foreign key are identity in the information schema tables, but I don't know MySQL this well
    – Serpiton
    Commented May 15, 2014 at 10:34

2 Answers 2


What I understood from your question is that you need to list all the parent and child relationship in your database, for that you can execute below query

    ke.referenced_table_name 'parent table',
    ke.referenced_column_name 'parent column',
    ke.table_name 'child table',
    ke.column_name 'child column',
    information_schema.KEY_COLUMN_USAGE ke
    ke.referenced_table_name IS NOT NULL
        AND table_schema = 'your_db_name'
ORDER BY ke.referenced_table_name;

Here your_db_name is my database.

  • I tried the query, It only gives reference table and refereed table. I want cardinality which we show in ER diagram Commented Mar 18, 2014 at 7:23

To complement on @Abdul Manaf's answer...

The relational model and SQL won't let you know exactly the cardinality of all your relationships. For instance, and unless you use very tricky techniques I can't think of at the moment, you won't be able to distinguish between a 1:1 relationship from a 1:0...1 relationship.

To have a 1:1 relationship, you would need to define two tables which reference each other for the same values. Although the standard SQL would allow you to do that, most databases will check FK constraings (REFERENCES) at INSERT/UPDATE time, and not have the test DEFERRED to the end of the transaction.

This means you are very likely to not have the information within the database to distinguish between a 1:0...1 and a 1:1 relationship. You will have to consider them always 1:0...1.

You normally can distinguish 1:0...1 relationships from 1:0...n relationships, because the column on the left table has got a UNIQUE constraint on it (or is a PRIMARY KEY, which implies a UNIQUE constraint), and the column on the right table has got a UNIQUE constraint (1:0...1 relationship) or does not have it (1:n relationship). Any kind of 1:2 or 1:3 relationship that can be represented in an ER diagram cannot be (for all practical purposes) represented in an SQL schema.

All the same applies, when you change columns with multi-columns, although all the queries to find out get much more complicated; because you have to take into account all columns at once.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.