MySQL getting blocking restrictions (konberentov) when deleting records, mini HowTo

Sometimes, I will not say that often, rather even rarely, a designated task arises, to find out - which tables in the tables block the deletion of the current record by their external links.





1. You need a MySQL version higher than 5.1.16, starting from this version, a table describing the REFERENTIAL_CONSTRAINTS consternates appeared in the official information_schema database.

2. The required request, for the sake of which this post was written:

 SELECT information_schema.referential_constraints.table_name, information_schema.key_column_usage.column_name FROM information_schema.referential_constraints INNER JOIN information_schema.key_column_usage ON information_schema.key_column_usage.constraint_schema = information_schema.referential_constraints.constraint_schema AND information_schema.key_column_usage.constraint_name = information_schema.referential_constraints.constraint_name WHERE information_schema.referential_constraints.constraint_schema = schema() AND delete_rule = 'RESTRICT' AND information_schema.referential_constraints.referenced_table_name = :table 

')

Here we select the tables and their foreign keys that have RESTRICT DELETE_RULE on the current table.



P.S. The mistake of the constrint, for which I was fussing with this garden, in PHP (PDO) I catch as follows:

 try { // do pdo execute() } catch (Exception $e) { /** * Find constraints error */ if ( (strpos($e->getMessage(), 'SQLSTATE[HY000]') !== false && strpos($e->getMessage(), 'General error: 1451')) || (strpos($e->getMessage(), 'SQLSTATE[23000]') !== false && strpos($e->getMessage(), 'Integrity constraint violation: 1451')) ) { // look for constraints } } 

P.P.S.

Live applications when needed:

The current database scheme includes 55 tables with large branching (the scheme has been refined many times over 3 years of life), 4 CMF client applications work with data from this database (different subcircuits), the database is checked for checking the referential integrity of the database tools, about the impossibility of deleting the record on which there are links also to indicate these links.