mysql - 如何获取链接到外键值的 InnoDB 表名？

Question

我有多个表（例如：A、B、C....等带有外键 ADDRESS_ID）链接到“地址”表，其中 ADDRESS_ID 是主键。表 A、B、C... 可能有或没有 address_ID 值。是否有一个查询可以让我在地址表中获取具有给定 ADDRESS_ID 的表名？请注意，这不是“地址”表中给定列的表名。如果在其他表中已经引用了地址表中给定的Address_ID 值，我需要表名。（我不知道所有可能引用了 address_id 的表。）

Address table 
ADDRESS_ID (PK) |   STREET ADDRESS_1   |   STREET_ADDRESS_2  |   CITY  |  ZIPCODE    


Table A
A_ID  |  ADDRESS_ID (FK) |  FIELD _1  |   FIELD_2

Table B
B_ID  |  ADDRESS_ID (FK)|  FIELD _!  |  FIELD_2

Table C
B_ID  |  ADDRESS_ID (FK) |  FIELD _! |  FIELD_2

更多表......

这是我尝试过的：

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
WHERE 
information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' AND
TABLE_CONSTRAINTS.TABLE_SCHEMA = 'flower_db' AND
TABLE_CONSTRAINTS.TABLE_NAME = 'address';

但这不支持我真正想要的。

标签： mysqlinnodb

Answer 1

 SELECT 'TableA' as Table_Has_Address       
 FROM TableA
 GROUP BY ADDRESS_ID
 HAVING COUNT(CASE WHEN ADDRESS_ID = @address_id THEN 1 END) > 0

 UNION ALL

 SELECT 'TableB' as Table_Has_Address       
 FROM TableB
 GROUP BY ADDRESS_ID
 HAVING COUNT(CASE WHEN ADDRESS_ID = @address_id THEN 1 END) > 0

 UNION ALL

 SELECT 'TableC' as Table_Has_Address       
 FROM TableC
 GROUP BY ADDRESS_ID
 HAVING COUNT(CASE WHEN ADDRESS_ID = @address_id THEN 1 END) > 0