在表之间查找外键约束列信息

Question

表A有一个外键约束（类型）指向表B的id。然而，类型不是null，而id可以为空。

我正在试图使用information_schema构建一个查询，该查询将查看外键约束并匹配列类型和可空列以查看它们是否同步，但是我的逻辑存在问题。

select kcu.table_name, kcu.column_name, c.column_type, c.is_nullable,kcu.referenced_table_name, kcu.referenced_column_name,c.column_type, c.is_nullable
from key_column_usage kcu
inner join columns c on c.table_schema=kcu.table_schema and c.column_name=kcu.column_name and c.table_name=kcu.table_name
where kcu.referenced_table_name='Table_B' and kcu.table_name='Table_A';

我知道这个语法是不正确的 - 这是目前为止我能够组合起来的所有内容。我希望它能够在数据库中的每个表格上执行，并按表名和列名排序。它可以排除列类型和可为空字段相同的列。

- JimRomeFan

Answer 1

在外键约束的一侧有一个可空列可能会有合法原因，但是这会比较涉及到的列的类型/可空属性。

SELECT
       kcu.constraint_schema
     , kcu.constraint_name
     , kcu.referenced_table_name
     , kcu.referenced_column_name
     , kcu.table_name
     , kcu.column_name
     , refcol.column_type referenced_column_type
     , childcol.column_type
     , refcol.is_nullable referenced_is_nullable
     , childcol.is_nullable

FROM information_schema.key_column_usage kcu
INNER JOIN information_schema.columns refcol
        ON refcol.table_schema = kcu.referenced_table_schema 
       AND refcol.table_name   = kcu.referenced_table_name
       AND refcol.column_name  = kcu.referenced_column_name 
INNER JOIN information_schema.columns childcol
        ON childcol.table_schema = kcu.table_schema 
       AND childcol.table_name   = kcu.table_name
       AND childcol.column_name  = kcu.column_name 

WHERE (
        refcol.is_nullable <> childcol.is_nullable
      OR
        refcol.column_type <> childcol.column_type
      )
AND kcu.TABLE_SCHEMA = 'rextester' #change this value to suit
ORDER BY
       kcu.table_name
     , kcu.column_name
;

查看一个工作示例（点击运行按钮）

- Paul Maxwell

---

refcol.column_type <> childcol.column_type - 我认为这是不可能的。 - Paul Spiegel

@Paul Spiegel 关于数据类型不匹配的问题，已经提出了条件。 - Paul Maxwell