Mysql: How to Determine Foreign Key Relationships Programmatically

MySQL: How to determine foreign key relationships programmatically?

There are two tables you can query to get this information: INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

Here's a query from the comments on the latter page linked above, which demonstrates how to get the info you seek.

SELECT CONCAT( table_name, '.', column_name, ' -> ', 
  referenced_table_name, '.', referenced_column_name ) AS list_of_fks 
FROM INFORMATION_SCHEMA.key_column_usage 
WHERE referenced_table_schema = 'test' 
  AND referenced_table_name IS NOT NULL 
ORDER BY table_name, column_name;

Use your schema name instead of 'test' above.

How to determine programmatically MySQL relationship type (1:1, 1:n, n:m) between tables?

After further research I found that although somehow MySQL Workbench is able to reverse engineer the 1:1 and 1:n relation even when is a non-identifying relationship where the attribute that references the foreign key isn't PK or UQ, this might be do to vendor specific (InnoDB) properties.

ALL other SQL reverse engineer tools tested showed non-identifying relationships as OneToMany even thou they where designed in MySQL WorkBench as OneToOne non-identifying. Assuming this I preformed a JOIN query to retrieve the necessary information to distinguish 1:1 from 1:n
So the SQL goes like the following:

Example for 'table1'

select INFORMATION_SCHEMA.COLUMNS.COLUMN_KEY, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
on INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME
where INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='table1' 
and referenced_table_name is not null

Finally...

Pseudo code

if (COLUMN_KEY == "PRI" || COLUMN_KEY == "UNI") { 
    //then you can assume is **OneToOne**
} else {
    //then you can assume is **OneToMany**
}

Hope this helps others with the struggle, feel free to add any suggestions or alternative ways of doing this, thanks all.

How can I get the foreign keys of a table in mysql

Simple way to get foreign keys for given table:

SELECT
    `column_name`, 
    `referenced_table_schema` AS foreign_db, 
    `referenced_table_name` AS foreign_table, 
    `referenced_column_name`  AS foreign_column 
FROM
    `information_schema`.`KEY_COLUMN_USAGE`
WHERE
    `constraint_schema` = SCHEMA()
AND
    `table_name` = 'your-table-name-here'
AND
    `referenced_column_name` IS NOT NULL
ORDER BY
    `column_name`;

Query a Table's Foreign Key relationships

This should work (or something close):

select table_name
from all_constraints
where constraint_type='R'
and r_constraint_name in 
  (select constraint_name
  from all_constraints
  where constraint_type in ('P','U')
  and table_name='<your table here>'); 

Is it possible to determine the table linked to by a foreign key?

Provided you are using MySQL 5.1 or later, you can use REFERENTIAL_CONSTRAINTS table from information schema. Something like:

select references_table_name
  from referential_constraints
 where table_name = 'your_table';

How to know relations between tables

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;

---

Related Topics