Search code examples
mysqlsqlclause

How to find all onDelete clauses with 'Restrict' value from a database?

I need to find and fix all the onDelete clauses of foreign keys, on a MySQL database, because all of them are now by default RESTRICT.

Can this be done by a MySQL-query?

At least to find all the foreign keys

UPDATE after @peterm answer:

SELECT * FROM information_schema.`REFERENTIAL_CONSTRAINTS` 
WHERE CONSTRAINT_SCHEMA = '%database_name%' AND delete_rule = 'RESTRICT';
Solution

  • You can use INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

    The REFERENTIAL_CONSTRAINTS table provides information about foreign keys.

    DELETE_RULE field is what you're looking for.