Lookup FK info on MyISAM and InnoDB Table

[Mike Ho]

I'm wondering if there is a way to retrieve Foreign Key declaration
information on both MyISAM and InnoDB tables.

Of course, I understand that MyISAM doesn't support Foreign Keys,
however, it does still parse FK declarations within a CREATE TABLE
statement, and moreover, it even creates an index on the column that
is the Foreign Key.

So my question is for this sample CREATE TABLE declaration:

CREATE TABLE table_name (
id INT NOT NULL AUTO_INCREMENT,
other_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (other_id) REFERENCES other_table (id))
TYPE = InnoDB --- OR --- TYPE = MyISAM
;

Can I get the Foreign Key information (meaing, the table and the
column that a FK is keyed to) from MySQL?

Doing a "DESC table_name" or "SHOW KEYS FROM table_name" indicates
that the FK column is Indexed, but doesn't really give much more
information other than that.

The only way I seem to be able to get *some* sort of FK info is if i
do a "SHOW CREATE TABLE table_name" (and even so, this only works on
InnoDB tables. For MySQL tables, it will show my foreign key
declaration as just a simple index).

Any ideas?

Thanks,
Mike

[Bill Karwin]

Mike Ho wrote:

I'm wondering if there is a way to retrieve Foreign Key declaration
information on both MyISAM and InnoDB tables.

According to the docs, a table in MyISAM format does not store any info
about the reference when you declare a foreign key. It creates an index
for the referencing field, but does not record the referenced table/column.

I think the fact that it parses the foreign key declaration is just a
convenience so one's schema scripts won't cause a syntax error. But
MyISAM effectively does not support referential integrity (though that
is mentioned as a possible future enhancement in the web page that lists
long-term MySQL goals).

Regards,
Bill K.

[Mike Ho]

Bill Karwin <bi**@karwin.com> wrote in message news:<cl********@enews2.newsguy.com>...

Mike Ho wrote:

I'm wondering if there is a way to retrieve Foreign Key declaration
information on both MyISAM and InnoDB tables.

According to the docs, a table in MyISAM format does not store any info
about the reference when you declare a foreign key. It creates an index
for the referencing field, but does not record the referenced table/column.

Thanks... but how about for InnoDB tables? So far the only way I can
"lookup" *any* sort of FK information is through the "SHOW CREATE
TABLE table_name" construct. Is there any other way?

Regards,
Mike

[Bill Karwin]

Mike Ho wrote:

how about for InnoDB tables? So far the only way I can
"lookup" *any* sort of FK information is through the "SHOW CREATE
TABLE table_name" construct. Is there any other way?

I don't know of any other way besides SHOW CREATE TABLE. I did a couple
of searches but couldn't find anything in SQL statements or in the API.

Regards,
Bill K.