One check constraint or multiple check constraints?

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)

---

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.

---

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.

---

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 NULL and SuperBatchId int NULL. Then my check constraint is

CHECK((BatchId  IS NOT NULL) OR (SuperBatchId  IS NOT NULL))

This is an example of check constraint for two column.