SQL Server - 对值来自另一个表的列进行 CHECK 约束
如何在列上设置 CHECK 约束，使其可接受的值范围来自另一个表，而无需硬编码？
这是一个简化的示例：
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'
我已经尝试创建以下格式的 CHECK 约束
TaskID in (Select Value FROM MetadataTable where class= 'A')
但这不受支持。
另一方面，('Val1', 'Val2') 中的 TaskID 在 SQL2k8 中充当检查约束（不在 SQL2000 中！），但由于硬编码，它不可接受。
如何实现我想要的，无论是通过 CHECK 约束还是其他我不知道的奇特机制？
附言。必须在数据库端，没有像某人向我建议的那样进行客户端检查。
How does one put a CHECK constraint on a column such that its range of acceptable values come from another table, without hardcoding?
Here's a simplified example:
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
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'
I already tried creating a CHECK constraint of the format
TaskID in (Select Value FROM MetadataTable where class= 'A')
BUT THIS IS NOT SUPPORTED.
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.
How to achieve what i want, whether via CHECK constraint or some other fancy mechanism that i am not aware of?
PS. Has to be on the database side, no client-side checking as has been suggested to me by someone.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这可能不是一个好的做法，但是您可以编写一个用户定义的函数，该函数接受您的 TaskID 作为参数，并根据 TaskID 是否落在 MetaDataTable 中提供的范围内来评估其值为 true 或 false。
这将允许您获得您正在寻找的功能 - CHECK 约束实际上只是旨在限制列范围的简单函数，并且它们的行为是在设计时考虑到这一点的，所以这就是为什么您不能在 SQL Server 中的检查约束内编写子查询。
但是，您可以在用户定义的函数中编写 SELECT 语句并从 CHECK 约束调用它。
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.
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.
You can however write a SELECT statement within a user defined function and call it from a CHECK constraint.
针对另一个表中的值的 CHECK 约束通常被设计为外键约束。这就是设计用于链接值表的机制。
CHECK 约束实际上只是为了定义
枚举所以我认为您无法执行您想要执行的操作，因为这确实是您尝试使用的错误功能那。
A CHECK constraint against values from another table would typically be designed as a foreign key constraint. That's the mechanism designed to link table on values.
CHECK constraints are really only designed to define
So I don't think you can do what you're trying to do, because that's really the wrong feature you're trying to use for that.