i would like to ask you how to get relations between tables ? I don't have access to script that creates all tables in database. So is it any SQL query that let me get relations and type of relations ? Is it possible or not, i have access to database but not to creating script ?
Asked
Active
Viewed 54 times
0
-
Do you mean you want to know the foreign keys? – jarlh Jan 07 '20 at 20:04
-
Yes, i mean foreign keys – Marcin Warzybok Jan 07 '20 at 20:10
-
https://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column#201678 – jarlh Jan 07 '20 at 20:12
-
I am checking, but it doesn't return type of relations, only display relations exists. – Marcin Warzybok Jan 07 '20 at 20:31
1 Answers
0
You can use INFORMATION_SCHEMA KEY_COLUMN_USAGE Table.
It takes some time to run, but gives you all foreign keys in your database
SELECT
`TABLE_NAME`,
`COLUMN_NAME`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM
`information_schema`.`KEY_COLUMN_USAGE`
WHERE `CONSTRAINT_SCHEMA` = 'your_database_name'
AND `REFERENCED_TABLE_SCHEMA` IS NOT NULL
AND `REFERENCED_TABLE_NAME` IS NOT NULL
AND `REFERENCED_COLUMN_NAME` IS NOT NULL
ORDER BY TABLE_NAME ;
"The KEY_COLUMN_USAGE table describes which key columns have constraints."
https://dev.mysql.com/doc/refman/8.0/en/key-column-usage-table.html
Klinger Soares
- 16
- 2
-
Ok, but it doesn't return me one to one or one to many. Is it possible to achive that – Marcin Warzybok Jan 07 '20 at 20:35
-
Take a look at [this question](https://stackoverflow.com/questions/26103542/how-to-determine-programmatically-mysql-relationship-type-11-1n-nm-betwee) – Klinger Soares Jan 07 '20 at 21:06
-
This doesn't work, it shows me that every relation is one to one – Marcin Warzybok Jan 07 '20 at 21:51