sqliteboolean-logicimplication

Checking if a column has a certain value, then restrict another by a list


I am wondering if there is any way for me to check if a column has a value, if the value is XXXXX then another column must be in the list of (A,B,C). something like:

CREATE TABLE test (a CHAR(60),b CHAR(60),Check (IF a == 'test' THEN b in ('a','b','c')));

Solution

  • In other words, in test rows, b must be in the list, while in non-test rows, b can be anything.

    So for the check to succeed, b must be in the list, or the row must not be a test row:

    CHECK (a <> 'test' OR b IN ('a', 'b', 'c'))
    

    Mathematically, a → b is the same as ¬a ∨ b.