List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:July 3 2010 3:00pm
Subject:Re: Retrieving table and field a foreign key references
View as plain text  

>For example, the following do not provide this information:

>- show create table address;
>- describe address;
>- select * from TABLE_CONSTRAINTS;
>- select * from key_column_usage;

For tables not using transactional engines like InnoDB, MySQL discards 
foreign key specs, otherwise see "Find child tables" and "Find parent 
tables" at http://www.artfulsoftware.com/infotree/queries.php.

PB

-----

On 7/2/2010 6:56 PM, Kris wrote:
> Hello,
>
> I am having trouble finding a way to retrieve the table and field a
> foreign key references by querying MySQL. If you look at the following
> example tables, there is no way after the tables are created to learn
> that:
>   - address.sid actually references state.id
>
>
> Is this possible ?
>
>
>
> For example, the following do not provide this information:
>
> - show create table address;
> - describe address;
> - select * from TABLE_CONSTRAINTS;
> - select * from key_column_usage;
>
> CREATE TABLE state
> (
> id VARCHAR(2) PRIMARY KEY,
> name TEXT
> );
>
> CREATE TABLE address
> (id INT PRIMARY KEY,
> address text,
> zipcode INT,
> sid VARCHAR(2),
> FOREIGN KEY(sid) REFERENCES state(id)
> );
>
>
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.439 / Virus Database: 271.1.1/2977 - Release Date: 07/02/10 06:35:00
>
Thread
Retrieving table and field a foreign key referencesKris3 Jul
  • Re: Retrieving table and field a foreign key referencesPeter Brawley3 Jul
  • Re: Retrieving table and field a foreign key referencesBaron Schwartz3 Jul