How to check for overlapping intervals when entering data in SQLite3?

I need to place a check in my SQLite3 database that ensures that the user cannot enter data with overlapping intervals.

For example:

hole #  Sample From    To 
1          1     1      2               
1          2     2      3               
1          3    2.2    2.9

With the example above I have checks in place that will catch any duplicate 'From' in each hole, but sample #3 is not a duplicate so it will not be caught, but it is an overlapping interval.

I don't want this for a query, but rather as a data-entry check built into the table.

So far I've tried adding a constraint check of ('From' NOT BETWEEN 'From' and 'To) but to no avail. I don't understand whether the check is trying to be applied on a row by row basis, which I want, or on a primary key basis.

Here is the table definition that I am trying:

 CREATE TABLE assay (
    BHID       TEXT    NOT NULL
                       CONSTRAINT [Check BHID] REFERENCES collar (BHID) ON DELETE CASCADE
                                                                        ON UPDATE CASCADE
                                                                        MATCH SIMPLE,
    [Sample #] TEXT    UNIQUE,
    [FROM]     NUMERIC NOT NULL
                       CONSTRAINT [Interval Check] CHECK ( ("TO" > "FROM") ),
    [TO]       NUMERIC NOT NULL,
    Ag         NUMERIC CONSTRAINT [Max Silver] CHECK ( (Ag < 1000) ),
    Zn         NUMERIC CONSTRAINT [Max Zinc] CHECK ( (Zn < 50) ),
    Pb         NUMERIC CONSTRAINT [Max Lead] CHECK ( (Pb < 50) ),
    Fe         NUMERIC,
    PRIMARY KEY (
        BHID,
        [FROM]
    )
);

And here is the table with the updated constraint (before commiting):

CREATE TABLE assay (
    BHID       TEXT    NOT NULL
                       CONSTRAINT [Check BHID] REFERENCES collar (BHID) ON DELETE CASCADE
                                                                        ON UPDATE CASCADE
                                                                        MATCH SIMPLE,
    [Sample #] TEXT    UNIQUE,
    [FROM]     NUMERIC NOT NULL
                       CONSTRAINT [Interval Check] CHECK ( ("TO" > "FROM") ) 
                       CONSTRAINT [Not Between] CHECK ( ('From' NOT BETWEEN 'From' AND 'To') ),
    [TO]       NUMERIC NOT NULL,
    Ag         NUMERIC CONSTRAINT [Max Silver] CHECK ( (Ag < 1000) ),
    Zn         NUMERIC CONSTRAINT [Max Zinc] CHECK ( (Zn < 50) ),
    Pb         NUMERIC CONSTRAINT [Max Lead] CHECK ( (Pb < 50) ),
    Fe         NUMERIC,
    PRIMARY KEY (
        BHID,
        [FROM]
    )
);

I deleted the data row with the conflicting data (From: 2.2, To: 2.9) and committed the change before trying to add the new constraint check. But it won't let me commit the new constraint, I believe because it is trying to apply it to the entire column.

So my question should be this: Is there a way to apply a constraint check on a row by row basis in sql?

Solution

In SQL, double quotes are used to quote table and column names; single quotes are used for string values. So the check

('FROM' NOT BETWEEN 'FROM' AND 'TO')

just compares these constant string values. This check always fails.

Anyway, a CHECK constraint can access only values in the current row. To be able to look at other rows, you have to use a trigger:

CREATE TRIGGER no_overlaps
BEFORE INSERT ON Assay
WHEN EXISTS (SELECT *
             FROM Assay
             WHERE "From" <= NEW."To"
               AND "To" >= NEW."From")
BEGIN
    SELECT RAISE(FAIL, "overlapping intervals");
END;