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

Re: Retrieving table and field a foreign key references

[Baron Schwartz]

Kris,

You can use SHOW CREATE TABLE.

On Fri, Jul 2, 2010 at 7:56 PM, Kris mk...@gmx.net 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)
 );


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=ba...@xaprb.com





-- 
Baron Schwartz
Percona Inc http://www.percona.com/
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=arch...@jab.org

Retrieving table and field a foreign key references

[Kris]

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