SQL 2008 - INFORMATION_SCHEMA视图中的外键约束

Question

我正在撰写一个C#单元测试，用于验证ORM类的字符串属性与目标数据库（始终为SQL 2008）以及数据映射到的类是否匹配。

检查指定外键在数据库中是否有效很容易：

    static private bool ConstraintExsits(string table, string column, ConstraintType constraintType)
    {
        string constraintTypeWhereClause;
        switch (constraintType)
        {
            case ConstraintType.PrimaryKey:
                constraintTypeWhereClause = "PRIMARY KEY";
                break;
            case ConstraintType.ForeignKey:
                constraintTypeWhereClause = "FOREIGN KEY";
                break;
            default:
                throw new ArgumentOutOfRangeException("constraintType");
        }

        var cmd = new SqlCommand(
                           @"SELECT a.CONSTRAINT_NAME
                            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a 
                            JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
                            WHERE a.TABLE_NAME = @table AND b.COLUMN_NAME = @column AND a.CONSTRAINT_TYPE = '" + constraintTypeWhereClause + "'",
                           Connection);
        cmd.Parameters.AddWithValue("@table", table.Trim('[').Trim(']'));
        cmd.Parameters.AddWithValue("@column", column.Trim('[').Trim(']'));
        return !string.IsNullOrEmpty((string)cmd.ExecuteScalar());
    }

现在看以下外键关系图： 我的问题是：如何从“主键/唯一键基表”和“主键/唯一键列”的一侧查询关系？我在INFORMATION_SCHEMA视图中找不到这些引用。

谢谢 J

- jaimie

Answer 1

这就是我想要的SQL!

SELECT 
FK_Table  = FK.TABLE_NAME, 
FK_Column = CU.COLUMN_NAME, 
PK_Table  = PK.TABLE_NAME, 
PK_Column = PT.COLUMN_NAME, 
Constraint_Name = C.CONSTRAINT_NAME 
FROM 
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
INNER JOIN 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
INNER JOIN 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
INNER JOIN 
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
INNER JOIN 
( 
    SELECT 
        i1.TABLE_NAME, i2.COLUMN_NAME 
    FROM 
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 
        INNER JOIN 
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
        ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' 
) PT 
ON PT.TABLE_NAME = PK.TABLE_NAME 

- jaimie

Answer 2

Jaimie的答案不能正确返回所有外键，如果被引用的表（外键所指向的表）有一个唯一键，因为它使用了 UNIQUE_CONSTRAINT_NAME 列。我建议:

SELECT 
    FK = fk.name, 
    FKTable = QUOTENAME(OBJECT_SCHEMA_NAME(fkcol.[object_id])) 
        + '.' + QUOTENAME(OBJECT_NAME(fkcol.[object_id])),
    FKCol = fkcol.name,
    ' references => ',
    PKTable = QUOTENAME(OBJECT_SCHEMA_NAME(pkcol.[object_id])) 
        + '.' + QUOTENAME(OBJECT_NAME(pkcol.[object_id])),
    PKCol = pkcol.name
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc
ON fk.[object_id] = fkc.constraint_object_id
INNER JOIN sys.columns AS fkcol
ON fkc.parent_object_id = fkcol.[object_id]
AND fkc.parent_column_id = fkcol.column_id
INNER JOIN sys.columns AS pkcol
ON fkc.referenced_object_id = pkcol.[object_id]
AND fkc.referenced_column_id = pkcol.column_id
ORDER BY fkc.constraint_column_id;

来源：

• 反对使用INFORMATION_SCHEMA视图的理由

- Eric Wood