Retrieving table and field a foreign key references

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Retrieving table and field a foreign key references

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Retrieving table and field a foreign key references

Kris,

You can use SHOW CREATE TABLE.

On Fri, Jul 2, 2010 at 7: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:
> =A0- 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)
> );
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dbaron@xaprb.c=
om
>
>



--=20
Baron Schwartz
Percona Inc
Consulting, Training, Support & Services for MySQL

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg