SQL 2008-INFORMATION_SCHEMA视图中的外键约束 [英] SQL 2008 - Foreign key constraints in the INFORMATION_SCHEMA view

问题描述

我正在编写一个c#单元测试，以针对目标数据库(始终为SQL 2008)和数据映射到的类验证ORM类的字符串属性.

I am writing a c# unit test that validates string properties for an ORM class against the target database, always SQL 2008, and the class that the data maps to.

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

Checking that a specified foreign key is valid in the DB is easy:

    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());
    }

现在采用以下外键关系:

Now take the following Foreign Key Relationships:

我的问题:如何从主/唯一键基本表"和主/唯一键列"侧查询关系?我无法在INFORMATION_SCHEMA视图中看到这些引用.

My question: How do I query the relationship from the 'Primary/Unique Key Base Table' and 'Primary/Unique Key Columns' side? I cannot see these referenced in the INFORMATION_SCHEMA views.

谢谢 J

推荐答案

这是我所追求的SQL！

This is the SQL that I was after!

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 

这篇关于SQL 2008-INFORMATION_SCHEMA视图中的外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！