使用查找表通过约束来限制值
我有一个查找表
Tbl_UserType_value_lookup:
UserTypeID |AllowedValue
-------------------------
1 |x
1 |y
2 |u
3 |a
（这表示类型 1 的用户只能输入 x 和 y 类型的值，而不能输入类型 x 和 y 的值输入 u 或 a..so on)
我有另一个表
Tbl_Activity:
UserID |userTypeID |value
---------------------------
现在在这个表中我如何限制 1 值如下使用 CHECK 约束的 Tbl_UserType_value_lookup 表？
还有其他办法吗？
I have a lookup table
Tbl_UserType_value_lookup:
UserTypeID |AllowedValue
-------------------------
1 |x
1 |y
2 |u
3 |a
(This says an user of Type 1 can enter the values of type x and y only and not of type u or a..so on)
I have another table
Tbl_Activity:
UserID |userTypeID |value
---------------------------
Now in this table how can I resrtict a user of type 1 to the values as per Tbl_UserType_value_lookup table using CHECK constraint ?
Is there any other way?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
假设您在
Tbl_UserType_value_lookup上有一个用于UserTypeID、允许值的唯一键，则您可以在Tbl_Activity上有一个引用这些列的复合外键。（即 UserType、Value 的组合必须存在于
Tbl_UserType_value_lookup上才能插入。这里对此有很多讨论：
在 SQL Server 2008 中创建复合外键
Assuming that you have a unique key on
Tbl_UserType_value_lookupforUserTypeID, Allowed Value, you could have a composite foreign key onTbl_Activitythat references these columns.(ie the combination of UserType, Value would have to exist on
Tbl_UserType_value_lookupto be insertable.There's quite a lot of discussion of this here:
Creating a composite foreign key in SQL Server 2008
我认为您无法创建动态
CHECK约束。使用外键将
Tbl_Activity链接到Tbl_UserType_value_lookup。您想要的是无法在数据结构中轻松执行的应用程序规则。
I think that you can't create a dynamic
CHECKconstraint.Use a foreign key to link
Tbl_ActivitytoTbl_UserType_value_lookup.What you want is an application rule that can't be easily enforced in the data structure.
Tbl_Activity：（
如果您的 SQL 平台支持复合外键，否则您将不得不破解一些丑陋的触发器和/或使用代理键）
顺便说一句：避免在表名和列名中使用混合大小写。通常，SQL不区分大小写，但如果您的数据库必须移动到区分大小写的安装，您会痛哭流涕。混合混合大小写和下划线被认为是 Bad_Style（大多数人）。
Tbl_Activity:
(If your SQL platform supports composite foreign keys, otherwise you'll have to hack some ugly triggers and/or use a surrogate key)
BTW: avoid MixedCase in table names and column names. Normally, SQL is not case-sensitive, but if your database has to be moved to a case-sensitive installation, you'll cry bitter tears. Mixing MixedCase and under_scores is considered Bad_Style (by most people).
CHECK 约束通过限制列接受的值来强制域完整性。它们与 FOREIGN KEY 约束类似，因为它们控制放入列中的值。 区别在于它们如何确定哪些值有效：FOREIGN KEY 约束从另一个表获取有效值列表，而 CHECK 约束从不基于另一列中的数据的逻辑表达式确定有效值。< /strong> 例如，可以通过创建 CHECK 约束来限制薪水列的值范围，该约束仅允许范围从 $15,000 到 $100,000 的数据。这可以防止工资输入超出正常工资范围。
使用外键！
更新：
有关详细信息，请查看此处。
CHECK constraints enforce domain integrity by limiting the values that are accepted by a column. They are similar to FOREIGN KEY constraints in that they control the values that are put in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression that is not based on data in another column. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range.
Use FOREIGN KEYS!
Update:
For more info, check HERE.