I have a table defined by the following SQL:
CREATE TABLE test (
id integer PRIMARY KEY NOT NULL UNIQUE,
status text NOT NULL,
enddate date,
/* Checks */
CHECK (status IN ("Current", "Complete"))
);
I'd like to add a constraint using CHECK that requires enddate to be non-null if the status is "Complete".
Is this possible? I am using SQLite v3.6.16.
How about:
CHECK (status = "Current" or (status = "Complete" and enddate is not null))
CHECK (status <> "Complete" or (status = "Complete" and enddate is not null)); notice that it has no mention of "Current" at all. You'll need to keep CHECK (status IN ("Current", "Complete")) as well to validate possible status values. If multiple statuses require enddate, you should be able to change <> and = to NOT IN and IN.(status <> "Complete" or enddate is not null) There's no need recheck the value of status, if you're checking the second half of the or statement, you already know status = "complete"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.
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.
