SQL Server-对值来自另一个表的列的CHECK约束 [英] SQL Server - CHECK constraint on a column where values come from another table

问题描述

如何将CHECK约束放在一列上，以使它的可接受值范围来自另一张表，而无需进行硬编码?

How does one put a CHECK constraint on a column such that its range of acceptable values come from another table, without hardcoding?

这是一个简化的示例:

OneManyTable
RoleID  TaskID
10      Val1
10      Val2
20      Val1
20      Val2


MetaDataTable
pkID    Class   Value
1       A       Val1
2       A       Val2
3       B       Val3
4       B       Val4

我想在OneManyTable.TaskID列上放置一个CHECK约束，以便可接受的值来自另一个表的列，即来自MetadataTable.Value，其中MetadataTable.class ='A'

I want to put a CHECK Constraint on OneManyTable.TaskID column such that acceptable values come from another tables's column, i.e. from MetadataTable.Value where MetadataTable.class= 'A'

我已经尝试创建格式的CHECK约束

I already tried creating a CHECK constraint of the format

TaskID in (Select Value FROM MetadataTable where class= 'A')

但不支持.

另一方面，('Val1'，'Val2')中的TaskID在SQL2k8中(而不是在SQL2000中！)用作检查约束，但由于硬编码而不能接受.

On the other hand TaskID in ('Val1', 'Val2') works as a check constraint in SQL2k8 (not in SQL2000 !), but its not acceptable due to hardcoding.

如何通过CHECK约束或我不知道的其他一些幻想机制来实现我想要的?

How to achieve what i want, whether via CHECK constraint or some other fancy mechanism that i am not aware of?

PS.必须在数据库方面，没有像有人建议的那样进行客户端检查.

PS. Has to be on the database side, no client-side checking as has been suggested to me by someone.

推荐答案

这可能不是一个好习惯，但是您可以编写一个用户定义的函数，该函数接受TaskID作为参数并将其评估为true或取决于TaskID是否落在您的MetaDataTable中提供的范围内.

It's probably not a good practice to get into, but you can write a user-defined function which accepts your TaskID as a parameter and have it evaluate to true or false depending upon whether or not the TaskID falls within the range provided in your MetaDataTable.

这将使您获得所需的功能-CHECK约束实际上仅是为了限制列范围而设计的简单函数，并且考虑到它们的行为而设计，因此这就是为什么无法在SQL Server的检查约束内编写子查询.

That'll allow you to get the functionality you're looking for - CHECK constraints are really just meant to be simple functions designed to limit the range of a column and their behavior was designed with that in mind, so that's why you can't write subqueries within a check constraint in SQL server.

但是，您可以在用户定义的函数中编写SELECT语句，然后从CHECK约束中调用它.

You can however write a SELECT statement within a user defined function and call it from a CHECK constraint.

这篇关于SQL Server-对值来自另一个表的列的CHECK约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！