Conditional SQLite check constraint?

sql sqlite constraints
Rezzie picture Rezzie · Apr 11, 2010 · Viewed 34.8k times · Source

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 that requires enddate to be non-null if the status is "Complete".

Is this possible? I am using SQLite v3.6.16.

Answer

Andomar picture Andomar · Apr 11, 2010

How about:

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

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
Is it possible to insert multiple rows at a time in an SQLite database?

In MySQL you can insert multiple rows like this: INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); However, I am getting an error when …

Read More