0

I have seen several posts on how to get foreign key references but not the actual relationship type like what is shown in MySQL's workbench ER diagram.

Currently I am using below sql to get references

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` = 'orders'; -- Only tables with foreign keys

What sql do I need to use to let me know if it is a 1:M, M:1, 1:1,

I have read How to determine cardinality of foreign key using mysql which hints at how to find this info.

Does any have an example sql statment to get the relationship type?

dan
  • 2,857
  • 6
  • 34
  • 60
  • For MySQL check: [enter link description here](https://stackoverflow.com/questions/273794/mysql-how-to-determine-foreign-key-relationships-programmatically) For SQL Server check: [enter link description here](https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server) – JBJ Apr 11 '19 at 22:16
  • This does not give tell you if it is 1:M M:M – dan Apr 11 '19 at 22:34
  • 1
    An M:M relationship cannot be modeled with a single FK. You need an additional table, so you will have two FK-s for one M:M relationship. At best, you can describe a FK as 1:M or 1:1, based on the existance of an unique index with the same columns in the same table as the FK (not in the referenced table, where it must always exist). – Razvan Socol Apr 12 '19 at 19:07

0 Answers0