如何检查外键是否存在？

Question

我想在一张表中查找外键，但是主键可能会被重命名/修改。如何确定表中的外键和主键？

相关代码：

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
         WHERE CONSTRAINT_NAME = 'FK_Name'

- Morteza

---

请查看以下的SO答案：https://dev59.com/b2sz5IYBdhLWcg3wg4Cv - praveen

Answer 1

随意使用此脚本，它会返回包含以下值的列表:

FKName  ParentTable  ParentColumnName    ReferencedTable    ReferencedColumnName

脚本：

SELECT fk.Name AS 'FKName'
          ,OBJECT_NAME(fk.parent_object_id) 'ParentTable'
          ,cpa.name 'ParentColumnName'
          ,OBJECT_NAME(fk.referenced_object_id) 'ReferencedTable'
          ,cref.name 'ReferencedColumnName'
    FROM   sys.foreign_keys fk
           INNER JOIN sys.foreign_key_columns fkc
                ON  fkc.constraint_object_id = fk.object_id
           INNER JOIN sys.columns cpa
                ON  fkc.parent_object_id = cpa.object_id
                AND fkc.parent_column_id = cpa.column_id
           INNER JOIN sys.columns cref
                ON  fkc.referenced_object_id = cref.object_id
                AND fkc.referenced_column_id = cref.column_id

- Yaroslav

Answer 2

这个查询应该能帮你入门 - 它会给你外键的名称、父表和参考表的名称以及列名：

select
    OBJECT_NAME(constraint_object_id),
    OBJECT_NAME(fkc.parent_object_id),
    scp.name,
    OBJECT_NAME(fkc.referenced_object_id),
    scr.name,
    fkc.constraint_column_id
from
    sys.foreign_key_columns fkc
        inner join
    sys.columns scp
        on
            fkc.parent_object_id = scp.object_id and
            fkc.parent_column_id = scp.column_id
        inner join
    sys.columns scr
        on
            fkc.referenced_object_id = scr.object_id and
            fkc.referenced_column_id = scr.column_id

如果你只涉及单列外键，那么你需要使用适当的 WHERE 子句从结果集中找到一行。

如果你有一个多列外键，那么你需要考虑跨多行匹配，并应用 GROUP BY OBJECT_NAME(constraint_object_id) 并使用 HAVING COUNT(*) = 列数。

- Damien_The_Unbeliever

Answer 3

对于那些想要通过information_schema来完成的人：

IF EXISTS(
    SELECT rc.*
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc 

    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 
        ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
        AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
        AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

    WHERE KCU1.TABLE_NAME = 'YOUR_TABLE_NAME' 
    AND rc.CONSTRAINT_NAME = 'YOUR_FK_NAME' 
    -- AND rc.CONSTRAINT_SCHEMA = 'dbo' -- Optional
)
     PRINT 'I exist' 

- Stefan Steiger