sql列出了主键的所有外键

Let's say the "user_id" field on "table_users" is primary key for 10 foreign keys on 10 other tables. I want to rename the field of primary key on "table_users", but it doesn't allow me to rename it because there are 10 foreign keys from 10 other tables are linked to it. May I know how to know which of the 10 tables has the foreign keys? What is the sql code to show the 10 tables which has the foreign keys?

假设“table_users”上的“user_id”字段是10个其他表上10个外键的主键。我想在“table_users”上重命名主键字段,但它不允许我重命名它,因为有10个其他表中的10个外键链接到它。我可以知道如何知道10个表中哪个有外键?什么是用于显示具有外键的10个表的sql代码?

I am using phpMyAdmin and InnoDB engine. I know SHOW ENGINE INNODB STATUS can do the job but it show me 1 table for each time, mean I need to repeat rename the primary key field for 10 times and run SHOW ENGINE INNODB STATUS for 10 times to find out all the 10 tables. Is there any other better solution to find out the 10 tables?

我正在使用phpMyAdmin和InnoDB引擎。我知道SHOW ENGINE INNODB STATUS可以完成这项工作,但它每次显示1个表,意味着我需要重复重命名主键字段10次并运行SHOW ENGINE INNODB STATUS 10次以找出所有10个表。找出10个表是否还有其他更好的解决方案?

2 个解决方案

#1

You can try querying INFORMATION_SCHEMA database:

您可以尝试查询INFORMATION_SCHEMA数据库:

select ku.* 
from INFORMATION_SCHEMA.table_constraints tc   
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku ON
(tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME and tc.CONSTRAINT_SCHEMA =   
ku.CONSTRAINT_SCHEMA )
where constraint_type='FOREIGN KEY' and ku.REFERENCED_TABLE_NAME =     
'your_table_name'

#2

As the mysql root user, you can get around this issue using

作为mysql root用户,您可以使用以解决此问题

SET foreign_key_checks = 0;

This disables the constraint system and will allow you to alter your table definition. Of course from there all your foreign key constraints will be broken, and need to be dropped and recreated.

这将禁用约束系统,并允许您更改表定义。当然,所有外键约束都将被打破,需要删除和重新创建。

SET foreign_key_checks = 1;

Will turn the constraint system back on.

将重新打开约束系统。

Mysql also has a data dictionary that can be queried. That was covered fully in This previous question so I'm not going to repeat any of that info.

Mysql还有一个可以查询的数据字典。在上一个问题中完全涵盖了这一点,所以我不打算重复任何这些信息。

#1