Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am working on the project where I have to analyse the MySQL schema. As a part of schema information, I want to list down relationship cardinality between different relations. Does MySQL stores cardinality information? If yes, Is there anyway to access those information?

share|improve this question
What is relationship cardinality ? Please explain – Abdul Manaf Mar 12 '14 at 10:13
It's relationship between two tables. One to many, many to many like that. – Nikunj Shukla Mar 14 '14 at 7:23
Have you tried below solution ? – Abdul Manaf Mar 14 '14 at 7:25
@AbdulManaf Yes I tried, It only gives reference table and refereed table. I want cardinality which we show in ER diagram. – Nikunj Shukla Mar 14 '14 at 12:38
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 May 15 '14 at 10:34

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.

share|improve this answer
I tried the query, It only gives reference table and refereed table. I want cardinality which we show in ER diagram – Nikunj Shukla Mar 18 '14 at 7:23

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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