Check Constraint based on other column value

Question

I have a table named Table1 with three columns col1, col2, col3. col1 can have one of the three values(1 or 2 or 3).

I need to add a check constraint such that it checks, if col1 has value 1 then the values of col2 and col3 should be same else if col1 has values 2 or 3, then col2 and col3 values may or may not be same.

Can anyone tell me how to add the constraint for this ?

Thanks in advance.

Answer 1

You can add a check constraint like this:

ALTER TABLE Table1 ADD CONSTRAINT chk_table1_cols
    CHECK ( (col1 = 1 AND col2 = col3) OR (col1 IN (2, 3)) );

Note that "is the same" presumes that the values are not NULL. That logic can be added, if you want to consider NULL values as equal.

Answer 2

You can express these condition with a series of logical operators:

(col IN (1, 2, 3)) AND (col2 = col3 OR col1 IN (2, 3)

Answer 3

I didn't verify (I don't have MySql) but in Oracle the following works:

ALTER TABLE Table1 ADD
(
  CONSTRAINT CHK_1 CHECK ((col1 BETWEEN 1 AND 3) AND (col1 <> 1 OR col2=col3))
)