How to count amount of rows referring to a foreign key in MySql?
foreign-keys, mysql
Solution
Displays all referenced tables with row counts
SELECT rc.table_name, t.TABLE_ROWS
FROM `REFERENTIAL_CONSTRAINTS` rc
INNER JOIN `TABLES` t ON t.TABLE_NAME = rc.TABLE_NAME
WHERE rc.REFERENCED_TABLE_NAME = "test"
Displays sum of all referenced tables row count
SELECT SUM(t.TABLE_ROWS) AS allReferencedTablesRowCount
FROM `REFERENTIAL_CONSTRAINTS` rc
INNER JOIN `TABLES` t ON t.TABLE_NAME = rc.TABLE_NAME
WHERE rc.REFERENCED_TABLE_NAME = "test"
Problem
Let's say a table like ``` CREATE TABLE `testdb`.`test` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ``` there are other tables may have foreign key referring to test.id column. The interesting thing is I don't know what table has such a foreign key and how many rows the table has. now I want to calculate amount of rows dispersing in tables that have foreign key to test.id. Is it possible? I think it's theoretically possible, otherwise MySql cannot do operations like `ON DELETE CASCADE`, `DELETE SET NULL` ...