5

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

Mathew Thompson
  • 55,877
  • 15
  • 127
  • 148
zx_wing
  • 1,918
  • 3
  • 26
  • 39
  • I don't think it is possible. Other tables have references to that table but not that table to the other refencences. – juergen d Apr 20 '12 at 20:42
  • I think not knowing that amount is part of the whole idea. – Radu Murzea Apr 20 '12 at 20:44
  • 1
    Do you want to dynamically figure out the foreign key constraints from the other tables? Do you want to do all your checks within MySQL? Are you afraid of using regexes? – Yzmir Ramirez Apr 20 '12 at 20:46
  • there must be a way to find out where are these references, otherwise ON DELETE CASCADE is impossible. however, mysql may not expose that interface to user. – zx_wing Apr 20 '12 at 20:47
  • @Yzmir Ramirez: I am not figuring out what table schema has that foreign key. I am trying to count the amount of rows tables have, where tables has that foreign key at runtime. – zx_wing Apr 20 '12 at 20:50
  • 2
    possible duplicate of [MySQL: How to I find all tables that have foreign keys that reference particular table.column AND have values for those foreign keys?](http://stackoverflow.com/questions/806989/mysql-how-to-i-find-all-tables-that-have-foreign-keys-that-reference-particular) – JohnFx Apr 20 '12 at 20:51
  • @JohnFx, yep the answer in that post will answer the OP's question. – Yzmir Ramirez Apr 20 '12 at 20:53
  • Thanks guys. Yes it's duplicate. Both topic got perfectly answered! – zx_wing Apr 20 '12 at 21:39

2 Answers2

6

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"
Nesim Razon
  • 9,684
  • 3
  • 36
  • 48
  • I am using mysql, does above only apply to SQLServer? – zx_wing Apr 20 '12 at 21:17
  • no it applies for mysql but above mysql 5.1 (The REFERENTIAL_CONSTRAINTS table was added in MySQL 5.1.10. The REFERENCED_TABLE_NAME column was added in MySQL 5.1.16.) – Nesim Razon Apr 20 '12 at 21:25
0

if you have foreign-key-constraints defined you can read them from the database schema to see which columns in which tables are linked to your primary key.

edit: check this (on the left side select "Database metadata" -> "Find child tables")

Andreas Linden
  • 12,489
  • 7
  • 51
  • 67
  • As @JohnFx mentioned, this is a duplicate of http://stackoverflow.com/questions/806989/mysql-how-to-i-find-all-tables-that-have-foreign-keys-that-reference-particular – Yzmir Ramirez Apr 20 '12 at 20:55