Limitation of detail from information_schema (by cascade of updates, with restriction of deletion)

Question

Almost all the information I need about the database I can find in information_schema

This time I needed to read the details of all foreign keys in the database with a single request . I found everything in the info_schema.key_Column_usage file, but could not find constraints like on delete, on update

I could do show create table for all individual tables. But is there a way to get this data through some sort of 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 does this job well, but simply skips such restrictions as on delete, on update . How can I get these values ​​so that I can get all the information about foreign keys in one request?

Answer 1

UPDATE_RULE and DELETE_RULE are what you asked for

it's a little too late, but it might help someone else, here is 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 2

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

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

Answer 3

Edit: information_schema.REFERENTIAL_CONSTRAINTS did not exist in earlier versions of mysql at the time the question was asked. Now yes, you can get information about all the restrictions. The accepted answer gives the solution as a request.

Original answer: Sorry, you cannot get the required data from information_schema, and you have to depend on show create table for each table. This is about the foreign keys that mysql provides you, and you could see that only show create table is invited to see the information about the external key. You can take a look at this link , but nothing else.

Answer 4

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

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