一项检查约束还是多项检查约束？
关于检查约束越少越好还是更多有什么建议吗？如果有的话应该如何分组？
假设我有 3 列 VARCHAR2(1 BYTE)，每一列都是一个“T”/“F”标志。我想向每列添加一个检查约束，指定只允许使用字符 IN ('T', 'F') 。
我应该有 3 个单独的检查约束，每列一个：
COL_1 IN ('T', 'F')
COL_2 IN ('T', 'F')
COL_3 IN ('T', 'F')
还是一个检查约束：
COL_1 IN ('T', 'F') AND COL_2 IN ('T', 'F') AND COL_3 IN ('T', 'F')
我的想法是最好将这三个约束分开，因为这些列在逻辑上彼此无关。我拥有检查多个列的检查约束的唯一情况是，一列中的值与另一列中的值之间是否存在某种关系，例如：
(PARENT_CNT > 0 AND PRIMARY_PARENT IS NOT NULL) OR (PARENT_CNT = 0 AND PRIMARY_PARENT IS NULL)
Any suggestions on whether fewer check constraints are better, or more? How should they be grouped if at all?
Suppose I have 3 columns which are VARCHAR2(1 BYTE), each of which is a 'T'/'F' flag. I want to add a check constraint to each column specifying that only characters IN ('T', 'F') are allowed.
Should I have 3 separate check constraints, one for each column:
COL_1 IN ('T', 'F')
COL_2 IN ('T', 'F')
COL_3 IN ('T', 'F')
Or a single check constraint:
COL_1 IN ('T', 'F') AND COL_2 IN ('T', 'F') AND COL_3 IN ('T', 'F')
My thoughts are it is best to keep these three separate, as the columns are logically unrelated to each other. The only case I would have a check constraint that examines more than one column is if there was some relationship between the value in one and the value in another, e.g.:
(PARENT_CNT > 0 AND PRIMARY_PARENT IS NOT NULL) OR (PARENT_CNT = 0 AND PRIMARY_PARENT IS NULL)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
保持分开，它们是不同的列。另外，错误消息会显示失败的检查约束名称，您将更好地知道问题出在哪里。未来的开发人员会困惑为什么它们都在一起，或者因为它们位于不同的列而没有注意到它们。
Keep the separate, they are different columns. Also, the error message will display the check constraint name that failed, and you will better know where the problem is. A future developer will be confused why they are all together, or not notice them since they are on a different column.
我建议根本不要使用 varchar。这不是人们在没有布尔数据类型的数据库中存储布尔值的标准做法。我建议您使用最小的整数类型，其中 0 = False，非零 = True。此时检查约束变得微不足道（甚至没有必要）。
解决批评：出于调试和维护原因，您应该制定 3 个限制（更好的错误、日志记录）。插入和更新时的性能可能会略有下降，但没什么大不了的。
I recommend not using a varchar at all. This is not a standard practice for how people store booleans in databases without a boolean data type. I recommend your smallest integer type where 0 = False and non-zero = True. Constraints become trivial to check at this point (even unnecessary).
Addressing criticisms: you should make 3 constraints for debugging and maintenance reasons (better errors, logging). Performance may be slightly lessened on insert and update but no big deal.
当列之间存在依赖关系时，可以对两列一起使用检查约束。
例如，当存在全局 id 和本地 id 时，如果您想要两者都不能为空的条件。并且允许其中之一为空或两者都不为空。但您需要验证其中之一不为空，或两者都不为空。
例子：
我有两列
BatchId int NULL和SuperBatchId int NULL。那么我的检查约束是这是两列检查约束的示例。
You can use check constraint for two columns together when there is a dependency between the columns.
For an example when there is a global id and local id, if you want a condition like both can't be null. And either one of them null or both not null is allowed. But you need to verify either one of them not null, or both not null.
Example:
I have two column
BatchId int NULLandSuperBatchId int NULL. Then my check constraint isThis is an example of check constraint for two column.