mysqldatabase-designwampentity-relationship

How to know relations between tables

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.

Solution

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