查询以查找数据库模式上的外键

I have two questions i need help with:

我有两个问题需要帮助:

I would like to have a query that displays foreign key relationships on tables in a schema. My whole goal is to find out if tables in a schema have foreign keys declared to establish relationships between the tables in the schema. For example, using "show crate table " will display the foreign keys on a table if declared by the developer. I tried to use "show table status on " and expected to see foreign key relationships in the comments column, but it was not the case. Anyway, is there a query i can use to check for foreign key relationships between tables in a schema?

我想有一个查询,在模式中的表上显示外键关系。我的总体目标是确定模式中的表是否具有声明的外键以在模式中的表之间建立关系。例如,如果开发人员声明,使用“show crate table”将显示表上的外键。我试图使用“show table status on”并期望在comments列中看到外键关系,但情况并非如此。无论如何,我是否可以使用查询来检查模式中表之间的外键关系?
What are some of the good queries i can use to perform analysis on database? I have been given an old database that i need to analyze so we can improve on its design and structure.

我可以使用哪些好的查询来对数据库进行分析?我得到了一个我需要分析的旧数据库,因此我们可以改进其设计和结构。

Thanks.

1 个解决方案

#1

You may use INFORMATION_SCHEMA for this:

您可以使用INFORMATION_SCHEMA:

SELECT 
  * 
FROM  
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE 
  CONSTRAINT_TYPE='FOREIGN KEY'

Possible types of constraint may be:

可能的约束类型可能是:

PRIMARY KEY for primary keys

主键的主键

FOREIGN KEY for foreign keys

外键的FOREIGN KEY

UNIQUE for unique constraints

UNIQUE用于独特约束

So you're interested in FOREIGN KEY type. This will show you which table on which column has the constraint, but won't show you targeted constraint column and table. To find them, you need to use another table, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS which has such information, so, basically, to reconstruct relation between tables, you'll need:

所以你对FOREIGN KEY类型感兴趣。这将显示哪个列具有约束的哪个表,但不会显示目标约束列和表。要找到它们,您需要使用另一个表,INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS,它具有此类信息,因此,基本上,要重建表之间的关系,您需要:

SELECT 
  t.TABLE_SCHEMA, 
  t.TABLE_NAME, 
  r.REFERENCED_TABLE_NAME 
FROM  
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t 
    JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS r 
    ON t.CONSTRAINT_NAME=r.CONSTRAINT_NAME 
WHERE 
  t.CONSTRAINT_TYPE='FOREIGN KEY'

But that's, again, is missing columns (because it doesn't belongs to those tables) and will show only relations via FK between tables. To reconstruct full relation (i.e. with columns involved) you'll need to refer to KEY_COLUMN_USAGE table:

但是,这又是缺少列(因为它不属于那些表),并且只会在表之间通过FK显示关系。要重建完整关系(即涉及列),您需要引用KEY_COLUMN_USAGE表:

SELECT 
  TABLE_SCHEMA, 
  TABLE_NAME, 
  COLUMN_NAME, 
  REFERENCED_TABLE_SCHEMA, 
  REFERENCED_TABLE_NAME, 
  REFERENCED_COLUMN_NAME 
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE 
  REFERENCED_TABLE_SCHEMA IS NOT NULL

This query will show all relations where referenced entity is not null, and, since it's applicable only in FK case - it's an answer to the question of finding FK relations. It's quite universal, but I've provided methods above since it may be useful to get info about PK or unique constraints too.

此查询将显示引用实体不为空的所有关系,并且,因为它仅适用于FK情况 - 它是查找FK关系问题的答案。这是非常普遍的,但我提供了上述方法,因为获取有关PK或唯一约束的信息可能也很有用。

#1