Constraint detail from information_schema (on upda

Question

Almost all the information I had needed about a database, I could find in information_schema

This time I needed to read details of all foreign keys in a database through single query I found every thing in information_schema.key_Column_usage but could not find the constraints like on delete, on update

I could do show create table for all individual tables. But is there any way to get these details through some select query like this?

SELECT CONSTRAINT_NAME, TABLE_NAME,COLUMN_NAME, REFERENCED_TABLE_NAME, 
REFERENCED_COLUMN_NAME FROM information_schema.`KEY_COLUMN_USAGE` WHERE 
table_schema = 'mydbname' AND referenced_column_name IS NOT NULL

It is doing the job well but just missing constraints like on delete, on update How can I get those values as well so that I can get all info about foreign keys in a single query?

Answer 1

Edit: information_schema.REFERENTIAL_CONSTRAINTS did not exist in earlier versions of mysql at the time question was asked. Now the answer is yes, you can get information about all constraints. Accepted answer gives the solution as query.

Original Answer: Sorry you can not get required details from information_schema and you have to depend upon show create table for each table Here is all about foreign keys which mysql provides you and you could see only show create table is suggested to see foreign key details. You may have a look at This link but nothing again.

Answer 2

UPDATE_RULE and DELETE_RULE is the thing you asked for

it's a little bit too late but it could help someone else, here the solution :

SELECT tb1.CONSTRAINT_NAME, tb1.TABLE_NAME, tb1.COLUMN_NAME,
tb1.REFERENCED_TABLE_NAME, tb1.REFERENCED_COLUMN_NAME, tb2.MATCH_OPTION,

tb2.UPDATE_RULE, tb2.DELETE_RULE

FROM information_schema.`KEY_COLUMN_USAGE` AS tb1
INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS AS tb2 ON
tb1.CONSTRAINT_NAME = tb2.CONSTRAINT_NAME
WHERE table_schema = 'sfa' AND referenced_column_name IS NOT NULL

Answer 3

If you are looking for (primary|foreign|unique) keys :

http://dev.mysql.com/doc/refman/5.5/en/table-constraints-table.html

Answer 4

Now, you can find foreign key constraint details in table INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

http://dev.mysql.com/doc/refman/5.5/en/referential-constraints-table.html