From: Peter Brawley
Date: July 3 2010 3:00pm
Subject: Re: Retrieving table and field a foreign key references
List-Archive: http://lists.mysql.com/mysql/222076
Message-Id: <4C2F5097.9020204@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit



>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
>