我正在编写一个 C# 单元测试,用于根据目标数据库(始终是 SQL 2008)以及数据映射到的类来验证 ORM 类的字符串属性。
检查指定的外键在数据库中是否有效很容易:
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 视图中引用的这些内容。
Thanks
J
这就是我想要的 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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)