如何在 Sql Server 中使用 INFORMATION_SCHEMA 或 sys 视图识别一对一（一对？）关系

Question

问题域 http://www.freeimagehosting.net/uploads/6e7aa06096.png

所以问题来了..使用TS​​QL和INFORMATION_SCHEMA或sys视图，如何识别1:0-1关系，例如FK_BaseTable_InheritedTable？

在一个具体的示例中，想象一个简单的 DTO - FK_JoinTable_ParentTable 将呈现为 ParentTable 对象上的 JoinTable 集合，而 FK_BaseTable_InheritedTable 将呈现为 BaseTable 对象上的 InheritedTable 对象（例如，继承是一个糟糕的选择，我知道，但不会回去）。

我能想到的最好的方法是一对多，与 FK_JoinTable_ParentTable 相同。我尝试了很多方法，包括（尝试）比较密钥，但效果不佳。

这是脚本。问题是，使用 INFO_SCHEMA 或 sys views ，将 FK_JoinTable_ParentTable 和 FK_JoinTable_Child 标识为一对多，将 FK_BaseTable_InheritedTable 标识为一对一/无。

试金石是能够区分 FK_BaseTable_InheritedTable 和 FK_JoinTable_ParentTable

CREATE TABLE [dbo].[Child](
 [ChildId] [int] NOT NULL,
 CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED 
(
 [ChildId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ParentTable](
 [ParentId] [int] NOT NULL,
 CONSTRAINT [PK_ParentTable] PRIMARY KEY CLUSTERED 
(
 [ParentId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[JoinTable](
 [PId] [int] NOT NULL,
 [CId] [int] NOT NULL,
 CONSTRAINT [PK_JoinTable] PRIMARY KEY CLUSTERED 
(
 [PId] ASC,
 [CId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[InheritedTable](
 [InheritedId] [int] NOT NULL,
 CONSTRAINT [PK_InheritedTable] PRIMARY KEY CLUSTERED 
(
 [InheritedId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[BaseTable](
 [BaseId] [int] NOT NULL,
 CONSTRAINT [PK_BaseTable] PRIMARY KEY CLUSTERED 
(
 [BaseId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[JoinTable]  WITH CHECK ADD  CONSTRAINT [FK_JoinTable_Child] FOREIGN KEY([CId])
REFERENCES [dbo].[Child] ([ChildId])

ALTER TABLE [dbo].[JoinTable] CHECK CONSTRAINT [FK_JoinTable_Child]

ALTER TABLE [dbo].[JoinTable]  WITH CHECK ADD  CONSTRAINT [FK_JoinTable_ParentTable] FOREIGN KEY([PId])
REFERENCES [dbo].[ParentTable] ([ParentId])

ALTER TABLE [dbo].[JoinTable] CHECK CONSTRAINT [FK_JoinTable_ParentTable]

ALTER TABLE [dbo].[BaseTable]  WITH CHECK ADD  CONSTRAINT [FK_BaseTable_InheritedTable] FOREIGN KEY([BaseId])
REFERENCES [dbo].[InheritedTable] ([InheritedId])

ALTER TABLE [dbo].[BaseTable] CHECK CONSTRAINT [FK_BaseTable_InheritedTable]

Answer 1

编辑：修复了外键查询中的小拼写错误，该错误不会影响答案，但会返回错误的唯一表

我得出但从未能够正确执行的原始结论是：

如果基础侧列
约束适合任何一个
基础边桌的独特约束
并且列数是相同的
这是一对一/无。

如果所有 FK 列都适合其中之一，则可以说同样的情况
具有更多列的唯一约束，如果所有这些列都包含
另一个 FK，但这超出了当前问题的范围。

这是我的执行问题。

我制定了一个可以产生正确结果的解决方案，但由于我不是 SQL 专家，我担心它相当混乱。也许我会把它作为另一个问题提出来供审查。

无论如何 - 这个脚本将识别所有 1:?数据库中的关系。

/*
    Will identify immediate 1:? fk relationships

*/
-- TODO: puzzle: work out the set-based equivalent 

SET NOCOUNT ON




BEGIN -- Get a table full of PK and UQ columns
    DECLARE @unique_keys TABLE
        (
          -- contains PK and UQ indexes
          [schema_name] NVARCHAR(128),
          table_name NVARCHAR(128),
          index_name NVARCHAR(128),
          column_id INT,
          column_name NVARCHAR(128),
          is_primary_key BIT,
          is_unique_constraint BIT,
          is_unique BIT
        )
    INSERT  INTO @unique_keys
            (
              [schema_name],
              table_name,
              index_name,
              column_id,
              column_name,
              is_primary_key,
              is_unique_constraint,
              is_unique
            )
        -- selects PK and UQ indexes
            SELECT  S.name AS [schema_name],
                    T.name AS table_name,
                    IX.name AS index_name,
                    IC.column_id,
                    C.name AS column_name,
                    IX.is_primary_key,
                    IX.is_unique_constraint,
                    IX.is_unique
            FROM    sys.tables AS T
                    INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id
                    INNER JOIN sys.indexes AS IX ON T.object_id = IX.object_id
                    INNER JOIN sys.index_columns AS IC ON IX.object_id = IC.object_id
                                                          AND IX.index_id = IC.index_id
                    INNER JOIN sys.columns AS C ON IC.column_id = C.column_id
                                                   AND IC.object_id = C.object_id
            WHERE   ( IX.is_unique = 1 )
                    AND ( T.name <> 'sysdiagrams' )
                    AND IX.is_unique = 1
            ORDER BY schema_name,
                    table_name,
                    index_name,
                    C.column_id
END



BEGIN -- Get a table full of FK columns

    DECLARE @foreign_key_columns TABLE
        (
          constraint_name NVARCHAR(128),
          base_schema_name NVARCHAR(128),
          base_table_name NVARCHAR(128),
          base_column_id INT,
          base_column_name NVARCHAR(128),
          unique_schema_name NVARCHAR(128),
          unique_table_name NVARCHAR(128),
          unique_column_id INT,
          unique_column_name NVARCHAR(128)
        )
    INSERT  INTO @foreign_key_columns
            (
              constraint_name,
              base_schema_name,
              base_table_name,
              base_column_id,
              base_column_name,
              unique_schema_name,
              unique_table_name,
              unique_column_id,
              unique_column_name
            )
            SELECT  FK.name AS constraint_name,
                    S.name AS base_schema_name,
                    T.name AS base_table_name,
                    C.column_id AS base_column_id,
                    C.name AS base_column_name,
                    US.name AS unique_schema_name,
                    UT.name AS unique_table_name,
                    UC.column_id AS unique_column_id,
                    UC.name AS unique_column_name
            FROM    sys.tables AS T
                    INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id
                    INNER JOIN sys.foreign_keys AS FK ON T.object_id = FK.parent_object_id
                    INNER JOIN sys.foreign_key_columns AS FKC ON FK.object_id = FKC.constraint_object_id
                    INNER JOIN sys.columns AS C ON FKC.parent_object_id = C.object_id
                                                   AND FKC.parent_column_id = C.column_id
                    INNER JOIN sys.columns AS UC ON FKC.referenced_object_id = UC.object_id
                                                    AND FKC.referenced_column_id = UC.column_id
                    INNER JOIN sys.tables AS UT ON FKC.referenced_object_id = UT.object_id
                    INNER JOIN sys.schemas AS US ON UT.schema_id = US.schema_id
            WHERE   ( T.name <> 'sysdiagrams' )
            ORDER BY base_schema_name,
                    base_table_name
END


DECLARE @constraint_name NVARCHAR(128),
    @base_schema_name NVARCHAR(128),
    @base_table_name NVARCHAR(128),
    @unique_schema_name NVARCHAR(128),
    @unique_table_name NVARCHAR(128)

-- The foreign key side of the constraint is always singular, we need to check from the perspective
-- of the unique side of the constraint.

-- for each FK constraint in DB
DECLARE tmpC CURSOR READ_ONLY
    FOR SELECT DISTINCT
                constraint_name,
                base_schema_name,
                base_table_name,
                unique_schema_name,
                unique_table_name
        FROM    @foreign_key_columns

OPEN tmpC
FETCH NEXT FROM tmpC INTO @constraint_name, @base_schema_name, @base_table_name, @unique_schema_name, @unique_table_name
WHILE @@FETCH_STATUS = 0
    BEGIN
        -- get the columns in the base side of the FK constraint
        DECLARE @fkc TABLE
            (
              column_name NVARCHAR(128)
            )
        DELETE  FROM @fkc

        INSERT  INTO @fkc ( column_name )
                SELECT  base_column_name
                FROM    @foreign_key_columns
                WHERE   constraint_name = @constraint_name

        -- check for one to one/none
        -- If the base side columns of the constraint fit into any one of the base side tables unique constraints
        -- AND the column count is the same then we have a one-to-one/none and should be realized as a singular 
        -- object reference

        -- I realize that if the base side unique constraint has more columns than the unique side unique constraint
        -- AND all of those columns DO represent a 1:? that would actually qualify but it seems like an edge case and
        -- beyond the scope of this question.

        DECLARE @uk_schema_name NVARCHAR(128),
            @uk_table_name NVARCHAR(128),
            @uk_index_name NVARCHAR(128),
            @is_may_have_a BIT
        SET @is_may_have_a = 0

        -- have to open another cursor over the unique keys of the base table - i want
        -- a distinct list of unique constraints for the base table

        DECLARE cKey CURSOR READ_ONLY
            FOR SELECT  DISTINCT
                        [schema_name],
                        table_name,
                        index_name
                FROM    @unique_keys
                WHERE   [schema_name] = @base_schema_name
                        AND table_name = @base_table_name

        OPEN cKey
        FETCH NEXT FROM cKey INTO @uk_schema_name, @uk_table_name, @uk_index_name
        WHILE @@FETCH_STATUS = 0
            BEGIN

                -- get the unique constraint columns
                DECLARE @pkc TABLE
                    (
                      column_name NVARCHAR(128)
                    )
                DELETE  FROM @pkc

                INSERT  INTO @pkc ( column_name )
                        SELECT  column_name
                        FROM    @unique_keys
                        WHERE   [schema_name] = @uk_schema_name
                                AND table_name = @uk_table_name
                                AND index_name = @uk_index_name

                -- if count is same and columns are same
                DECLARE @count1 INT, @count2 INT
                SELECT  @count1 = COUNT(*) FROM    @fkc
                SELECT  @count2 = COUNT(*) FROM    @pkc

                IF @count1 = @count2 
                    BEGIN 
                        -- select all from both on name and exclude mismatches
                        SELECT  @count1 = COUNT(*)
                        FROM    @fkc F
                                FULL OUTER JOIN @pkc P ON f.column_name = p.column_name
                        WHERE   NOT p.column_name IS NULL AND NOT f.column_name IS NULL 

                        IF @count1 = @count2 
                            BEGIN
                                -- the base side of the fk constraint corresponds exactly to 
                                -- at least on unique constraint making it effectively 1:?
                                SET @is_may_have_a = 1
                                BREAK
                            END
                    END
                FETCH NEXT FROM cKey INTO @uk_schema_name, @uk_table_name, @uk_index_name
            END

        CLOSE cKey
        DEALLOCATE cKey

        IF @is_may_have_a = 1 
            PRINT 'for ' + @unique_schema_name + '.' + @unique_table_name + ' constraint ' + + @constraint_name + ' is 1:? ' 

        FETCH NEXT FROM tmpC INTO @constraint_name, @base_schema_name, @base_table_name, @unique_schema_name, @unique_table_name
    END

CLOSE tmpC
DEALLOCATE tmpC

要查看更精细的测试数据库的结果，请参阅 TSQL：识别1：？关系