MySQL 5.0 – INFORMATION_SCHEMA (cont’d)

I mentioned “fuzzy foreign key” matches in my post the other day, and thought that I’d give it a crack tonight. The aim of the fuzzy match is to try and find relationships when a) They are InnoDB and haven’t been created or b) Foreign keys are not avilable to the storage engine that is being used.

Most DBA’s will know their schemas pretty well, this however should be pretty handy for consultants or anyone starting fresh at a new job / on a new database etc.

It should be noted that this will only “fuzzy” match against a schema that follows strict naming conventions – Where the tablename and “id” is used for the primary key column, and the same name is used for the foreign key in the child table. For instance, given the following example tables:

mysql> desc company; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | company_id | int(11) | NO | PRI | NULL | auto_increment | | company_name | varchar(100) | YES | MUL | NULL | | +--------------+--------------+------+-----+---------+----------------+ mysql> desc contacts; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | contact_id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | company_id | int(11) | YES | MUL | NULL | | | direct_line | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+

Here’s what I came up with:

SELECT t1.table_name as parent_table,
t1.column_name as primary_key,
t2.table_name as child_table,
t2.column_name as foreign_key
FROM information_schema.columns t1
JOIN information_schema.columns t2 USING (column_name,data_type,table_schema)
WHERE t1.table_name != t2.table_name
AND t1.column_key = 'PRI'
AND t1.table_schema NOT IN ('information_schema','mysql')
ORDER BY t1.table_name

+-----------------+-------------+-------------+-------------+
| parent_table | primary_key | child_table | foreign_key |
+-----------------+-------------+-------------+-------------+
| company | company_id | contacts | company_id |

.....

You can change the table_schema WHERE clause as required, or swap it out for “AND t1.table_name LIKE ‘wp%'” fpr example..
This makes use of the INFORMATION_SCHEMA.COLUMNS view.

Of course, there’s no guarentee this will work in all cases. People seem to have an in-built urge to use some of the most outrageously stupid names for their objects in so many cases…

Edit 21-06-05 Removed extra comma in second query (left from my own testing where I excluded extra db names) – Thanks inviso