Does SQLite check constraint allow checking against existing data?-CodePudding

Does SQLite check constraint supports checking against existing data? I just read this article, https://www.sqlitetutorial.net/sqlite-check-constraint/, my impression is the check can only apply on the data being inserted, not including existing data.

Here is my use case, suppose the database already have several rows of integer data and they all follow this rule, no data is less than half of the smallest data. Now I like to enforce this rule on all the data to be inserted.

For example, say, I have the following data already, 10, 11, 12, 15, 16. It is ok to add 6 since it is larger than 5 which is half of the smallest data which is 10; but it should not allow adding 3 because it is less than 5.

Thanks!

CodePudding user response：

From CREATE TABLE/CHECK constraints:

The expression of a CHECK constraint may not contain a subquery.

Since the only way to get the min value of the column is a query this means that it is not possible to do it with a CHECK constraint.

Use a trigger:

CREATE TRIGGER trg_value BEFORE INSERT ON tablename
BEGIN
   SELECT
      CASE
        WHEN NEW.value < (SELECT MIN(value) FROM tablename) / 2
          THEN RAISE (ABORT, 'Invalid value')
      END;
END;

See the demo.