Как проверить перекрытие интервалов при вводе данных в SQLite3?

Мне нужно поставить отметку в моей базе данных SQLite3, которая гарантирует, что пользователь не может вводить данные с перекрывающимися интервалами.

Например:

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

В приведенном выше примере у меня есть проверки, которые будут обнаруживать любые дубликаты «От» в каждом отверстии, но образец № 3 не является дубликатом, поэтому он не будет обнаружен, но это перекрывающийся интервал.

Мне это нужно не для запроса, а для проверки ввода данных, встроенной в таблицу.

До сих пор я пытался добавить проверку ограничений ('From' NOT BETWEEN 'From' and 'To), но безрезультатно. Я не понимаю, применяется ли проверка построчно, как я хочу, или на основе первичного ключа.

Вот определение таблицы, которое я пробую:

 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]
    )
);

А вот таблица с обновленным ограничением (до фиксации):

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]
    )
);

Я удалил строку данных с конфликтующими данными (From: 2.2, To: 2.9) и зафиксировал изменение, прежде чем пытаться добавить новую проверку ограничений. Но это не позволяет мне зафиксировать новое ограничение, я полагаю, потому что оно пытается применить его ко всему столбцу.

Итак, мой вопрос должен заключаться в следующем: есть ли способ применить проверку ограничений по строкам в sql?


sqlite sqlitestudio
person joswhite    schedule 02.06.2017    source источник
comment
Что ты пробовал? Где ты застрял?   -  person CL.    schedule 03.06.2017
comment
Я пробовал («От» НЕ МЕЖДУ «От» и «Кому»), но это не работает, и я не уверен, что делать дальше.   -  person joswhite    schedule 03.06.2017
comment
Покажите фактическое определение таблицы и фактическое ограничение, которое вы пытались добавить.   -  person CL.    schedule 03.06.2017
comment
Прошу прощения за некачественное объяснение моей проблемы. Я новичок в sql и базах данных в целом, и я уверен, что вы можете сказать.   -  person joswhite    schedule 03.06.2017

Ответы (1)


arrow_upward
0
arrow_downward

В SQL для обозначения имен таблиц и столбцов используются двойные кавычки; одинарные кавычки используются для строковых значений. Итак, проверка

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

просто сравнивает эти постоянные строковые значения. Эта проверка всегда терпит неудачу.

В любом случае ограничение CHECK может обращаться только к значениям в текущей строке. Чтобы иметь возможность просматривать другие строки, вы должны использовать триггер:

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;
person CL.    schedule 03.06.2017
comment
Спасибо за продуктивный отзыв и ответ! - person joswhite; 03.06.2017