Is it possible to use Mysql foreign keys in InnoDB tables for inverse lookup?

Question

Hi!

I wonder if it is possible to use FK's in Mysql (InnoDB) for inverse lookup.

The reason - I want to read an XML-like structure from the db (using one table per "layer"), but be able to do this dynamically. I want to be able to update the xml structure by adding a new table and setting a FK constraint.

To clarify, say we have a table "parent" with and id (parent_id) and two other columns (k1 and k2). The xml would look like (omitting id):

<parent>
 <k1>v1</k1>
 <k2>v2</k2>
</parent>

Now we add a child table with a foreign key referencing parent_id and one other column (ck1). The same query (with some processing afterwards) should now give:

<parent>
 <k1>v1</k1>
 <k2>v2</k2>
 <child>
  <ck1>cv1</ck1>
 </child>
</parent>

Is this possible? To "SELECT * FROM parent_table" and set some kind of parameter to also return the child rows wich points back with a FK?

Many thanks! /Victor

Answer 1

After reading up a lot, one option would be to use something like:

SELECT
  referenced_table_name parent,
  table_name child,
FROM
  information_schema.KEY_COLUMN_USAGE
WHERE
  referenced_table_name IS NOT NULL

This gives all tables having children. Filtering to only return children to the requested parent would of course be easy. On the downside, however, the above extra query (and processing) would be required.

I would still love to get a "better" solution, but this may at least be a start ;)