oracle 一个检查约束还是多个检查约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2457344/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
One check constraint or multiple check constraints?
提问by aw crud
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.
假设我有 3 列VARCHAR2(1 BYTE)，每列都是一个 'T'/'F' 标志。我想向每列添加一个检查约束，指定只IN ('T', 'F')允许使用字符。
Should I have 3 separate check constraints, one for each column:
我应该有 3 个单独的检查约束，每列一个：
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)
回答by KM.
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.
保持分开，它们是不同的列。另外，错误信息会显示失败的检查约束名称，您将更好地知道问题出在哪里。未来的开发人员会困惑为什么它们都在一起，或者因为它们在不同的列上而不会注意到它们。
回答by marr75
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).
我建议根本不要使用 varchar。这不是人们如何在没有布尔数据类型的数据库中存储布尔值的标准做法。我推荐您的最小整数类型，其中 0 = False 和非零 = True。此时检查约束变得微不足道（甚至不必要）。
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.
解决批评：您应该出于调试和维护原因（更好的错误、日志记录）设置 3 个约束。插入和更新的性能可能会略有下降，但没什么大不了的。
回答by Shaharban T A
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.
例如，当有全局 id 和本地 id 时，如果您想要两者之类的条件不能为空。并且允许其中之一为 null 或两者都不为 null。但是您需要验证其中一个不为空，或者两个都不为空。
Example:
I have two column BatchId int NULLand SuperBatchId int NULL. Then my check constraint is
示例：我有两列BatchId int NULL和SuperBatchId int NULL. 然后我的检查约束是
CHECK((BatchId IS NOT NULL) OR (SuperBatchId IS NOT NULL))
This is an example of check constraint for two column.
这是两列检查约束的示例。
