One check constraint or multiple check constraints?
Posted
by RenderIn
on Stack Overflow
See other posts from Stack Overflow
or by RenderIn
Published on 2010-03-16T19:13:56Z
Indexed on
2010/03/18
2:11 UTC
Read the original article
Hit count: 496
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)
© Stack Overflow or respective owner