470,593 Members | 2,411 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,593 developers. It's quick & easy.

Lookup FK info on MyISAM and InnoDB Table

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
Jul 20 '05 #1
3 2552
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.
Jul 20 '05 #2
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
Jul 20 '05 #3
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.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Sandra | last post: by
5 posts views Thread by steve | last post: by
3 posts views Thread by siliconmike | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.