如何修改T-SQL查询以识别SQL Server数据库关系的基数类型

以下是修改后的T-SQL查询，可自动识别外键关系的基数类型（一对一、一对多、多对多）：

SELECT
    FK.[name] AS ForeignKeyConstraintName,
    SCHEMA_NAME(FT.schema_id) + '.' + FT.[name] AS ForeignTable,
    STUFF(ForeignColumns.ForeignColumns, 1, 2, '') AS ForeignColumns,
    SCHEMA_NAME(RT.schema_id) + '.' + RT.[name] AS ReferencedTable,
    STUFF(ReferencedColumns.ReferencedColumns, 1, 2, '') AS ReferencedColumns,
    -- 判断关系基数类型
    CASE
        -- 一对一：子表外键列有唯一约束，父表被引用列是主键/唯一约束
        WHEN EXISTS (
            SELECT 1
            FROM sys.key_constraints KC
            INNER JOIN sys.index_columns IC ON KC.parent_object_id = IC.object_id AND KC.unique_index_id = IC.index_id
            WHERE KC.parent_object_id = FT.object_id
              AND KC.type = 'UQ'
              AND IC.column_id IN (SELECT parent_column_id FROM sys.foreign_key_columns WHERE constraint_object_id = FK.object_id)
        ) AND EXISTS (
            SELECT 1
            FROM sys.key_constraints KC
            WHERE KC.parent_object_id = RT.object_id
              AND KC.type IN ('PK', 'UQ')
              AND KC.object_id = (SELECT referenced_object_id FROM sys.foreign_key_columns WHERE constraint_object_id = FK.object_id)
        ) THEN 'one-to-one'
        -- 一对多：父表被引用列是主键/唯一约束，子表外键列无唯一约束
        WHEN EXISTS (
            SELECT 1
            FROM sys.key_constraints KC
            WHERE KC.parent_object_id = RT.object_id
              AND KC.type IN ('PK', 'UQ')
              AND KC.object_id = (SELECT referenced_object_id FROM sys.foreign_key_columns WHERE constraint_object_id = FK.object_id)
        ) THEN 'one-to-many'
        -- 多对多：当前表是中间关联表，含两个外键且组合为表主键
        WHEN EXISTS (
            SELECT 1
            FROM sys.tables T
            INNER JOIN sys.foreign_keys FK1 ON T.object_id = FK1.parent_object_id
            INNER JOIN sys.foreign_keys FK2 ON T.object_id = FK2.parent_object_id
            INNER JOIN sys.key_constraints PK ON T.object_id = PK.parent_object_id
            WHERE T.object_id = FT.object_id
              AND FK1.object_id <> FK2.object_id
              AND PK.type = 'PK'
              AND EXISTS (
                  SELECT 1 FROM sys.index_columns IC1 WHERE IC1.object_id = PK.parent_object_id AND IC1.index_id = PK.unique_index_id AND IC1.column_id IN (SELECT parent_column_id FROM sys.foreign_key_columns WHERE constraint_object_id = FK1.object_id)
              )
              AND EXISTS (
                  SELECT 1 FROM sys.index_columns IC2 WHERE IC2.object_id = PK.parent_object_id AND IC2.index_id = PK.unique_index_id AND IC2.column_id IN (SELECT parent_column_id FROM sys.foreign_key_columns WHERE constraint_object_id = FK2.object_id)
              )
        ) THEN 'many-to-many'
        ELSE 'unknown'
    END AS RelationType
FROM
    sys.foreign_keys FK
INNER JOIN 
    sys.tables FT ON FT.object_id = FK.parent_object_id
INNER JOIN 
    sys.tables RT ON RT.object_id = FK.referenced_object_id
CROSS APPLY
    (SELECT
         ', ' + iFC.[name] AS [text()]
     FROM
         sys.foreign_key_columns iFKC
     INNER JOIN 
         sys.columns iFC ON iFC.object_id = iFKC.parent_object_id
                         AND iFC.column_id = iFKC.parent_column_id
     WHERE
         iFKC.constraint_object_id = FK.object_id
     ORDER BY
         iFC.[name]
     FOR XML PATH('')) ForeignColumns (ForeignColumns)
CROSS APPLY
    (SELECT
         ', ' + iRC.[name] AS [text()]
     FROM
         sys.foreign_key_columns iFKC
     INNER JOIN 
         sys.columns iRC ON iRC.object_id = iFKC.referenced_object_id
                         AND iRC.column_id = iFKC.referenced_column_id
     WHERE
         iFKC.constraint_object_id = FK.object_id
     ORDER BY
         iRC.[name]
     FOR XML PATH('')) ReferencedColumns (ReferencedColumns)

逻辑说明： #

• 一对一：子表的外键列存在唯一约束，同时父表的被引用列是主键或唯一约束，确保两边记录一一对应。
• 一对多：父表的被引用列是主键或唯一约束，但子表外键无唯一约束，允许父表一条记录对应子表多条记录。
• 多对多：外键所在表为中间关联表，包含两个指向不同主表的外键，且这两个外键的组合是中间表的主键，用于连接两个表的多对多关系。
• unknown：无法通过系统元数据判断的非标准约束场景。

内容的提问来源于stack exchange，提问作者Milad Ghiass Beygi