How to know relations between tables

Question

I have a database in MySQL created by someone. I don't have any documentation of the database.

How can I know the relationship between the tables?

Is there any query or a procedure to generate a report so that it's easy to find the relations?

I can look into Schema information and manually figure it out, but it would be great if I could generate a relationship report.

Answer 1

You can get an overview in MySql Workbench by doing the steps below:

1. Go to "Database" Menu option.
2. Select the "Reverse Engineer" option.
3. A wizard will be opened and will generate an EER Diagram which shows up

Answer 2

The better way as programmatically speaking is gathering data from INFORMATION_SCHEMA.KEY_COLUMN_USAGE table as follows:

SELECT    `TABLE_SCHEMA`,                          -- Foreign key schema   `TABLE_NAME`,                            -- Foreign key table   `COLUMN_NAME`,                           -- Foreign key column   `REFERENCED_TABLE_SCHEMA`,               -- Origin key schema   `REFERENCED_TABLE_NAME`,                 -- Origin key table   `REFERENCED_COLUMN_NAME`                 -- Origin key column FROM   `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`  -- Will fail if user don't have privilege WHERE   `TABLE_SCHEMA` = SCHEMA()                -- Detect current schema in USE    AND `REFERENCED_TABLE_NAME` IS NOT NULL; -- Only tables with foreign keys 

and another one is

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;