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