One check constraint or multiple check constraints?
- by RenderIn
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)
