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

Related posts about database-design

Related posts about check-constraints