Re: Retrieving table and field a foreign key references

[Peter Brawley]

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=arch...@jab.org