4

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 – 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
  • 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 '14 at 10:34
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.referenced_column_name 'parent column',
    ke.table_name 'child table',
    ke.column_name 'child column',
    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.

  • 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
1

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, privacy policy and cookie policy

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