Conditional SQLite check constraint?
How about:
CHECK (status = "Current" or (status = "Complete" and enddate is not null))
CREATE TABLE test ( id integer PRIMARY KEY, status text NOT NULL CHECK (status IN ('Current', 'Complete')), enddate date NOT NULL);
This will work in SQLite, with the CHECK
constraint written inline. I changed double quotes to apostrophes so it can be used in PHP.
There's nothing stopping you from having multiple CHECK
constraints on a single table. IMO the simplest and most easily expandable solution:
CHECK (status IN ("Current", "Complete"))CHECK (status <> "Complete" OR enddate IS NOT NULL)
This uses the fact that if A then B is logically equivalent to either not A or B.