CODEWITHSUNDEEP
sqlsqliteconstraints
Rezzie
Rezzie

Reputation: 4993

Conditional SQLite check constraint?

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.

Upvotes: 37

Views: 44460

Answers (3)

relatively_random
relatively_random

Reputation: 5146

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.

Upvotes: 4

Ensarija
Ensarija

Reputation: 57

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.

Upvotes: 0

Andomar
Andomar

Reputation: 238076

How about:

CHECK (status = "Current" or (status = "Complete" and enddate is not null))

Upvotes: 50

Related Questions