Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
2  
What is relationship cardinality ? Please explain –  Abdul Manaf Mar 12 at 10:13
    
It's relationship between two tables. One to many, many to many like that. –  Nikunj Shukla Mar 14 at 7:23
    
Have you tried below solution ? –  Abdul Manaf Mar 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 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 May 15 at 10:34

1 Answer 1

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

SELECT 
    ke.referenced_table_name 'parent table',
    ke.column_name 'parent coulmn',
    ke.table_name 'child table',
    ke.referenced_column_name 'child coulmn',
    ke.constraint_name
FROM
    information_schema.KEY_COLUMN_USAGE ke
WHERE
    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 at 7:23

Your Answer

 
discard

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.