One check constraint or multiple check constraints?
check-constraints, database-design, oracle, sql
Solution
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.
Problem
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) ```