How to know relations between tables
Solution 1
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;
Solution 2
You can get an overview in MySql Workbench by doing the steps below:
- Go to "Database" Menu option.
- Select the "Reverse Engineer" option.
- A wizard will be opened and will generate an EER Diagram which shows up
Solution 3
Try out SchemaSpy (http://schemaspy.sourceforge.net/):
SchemaSpy is a Java-based tool (requires Java 5 or higher) that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format.
Here is a screenshot of the HTML page of the sample output from http://schemaspy.sourceforge.net/sample/ :
There is also a nice GUI if you do not want to use the command line: http://schemaspygui.sourceforge.net/
Both tools are open source and in my opinion very lightweight and easy to use. I used them several times when I was in situations that you described: To get an overview of the schema and even some details to dive deeper. (Take a look at the "Anomalies" report.)
Update
Be sure to check out the upcoming version of SchemaSpy at http://schemaspy.org
Solution 4
One more valuable option may be if you just install mysql workbench.( refers to) And try "Create EER models from database" .You will surely able to see relations among tables.
Solution 5
Do you have the SELECTs
that use the database? That may be the best source of the relationships.
Related videos on Youtube
Narasimha Maiya
By Day:- A working in a company. By Night:- Planing for the weekends. By Weekends:- Party, Movie, and Roam Around For Fun:- Movies, Traveling, PC Games.
Updated on July 06, 2022Comments
-
Narasimha Maiya almost 2 years
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.
-
Smita Ahinave about 8 yearsYou may use mysql workbench and get ER diagram with all relationships
-
user2260040 about 8 yearspossible dupe of... stackoverflow.com/questions/20855065/…
-
pojo-guy about 8 yearsRelations are not necessarily available in the database - it is not uncommon in enterprise transactional systems for RI to be stripped out for performance purposes. Also, some highly customizable systems (PeopleSoft, SAP, Maximo) maintain RI in the application and have tables to define the relationships.
-
philipxy over 5 yearsThis isn't clear. Do you want the FKs that were declared & are recorded in DBMS metadata, or do you mean guessing at ones would or should have been declared by looking at data?
-
-
Narasimha Maiya about 8 yearsI'm using xamp is it will work if write the same query their?
-
Ankit Agrawal about 8 years@Narasimha Maiya i haven't worked on xamp yet.you can try it on xamp .Thanks