sql - 如何使用条件替换冲突(对于SQLite)？

Question

我下面有一个SQL创建一个表。如果名称冲突，它将替换。

CREATE TABLE IF NOT EXISTS MYTABLE (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    NAME TEXT NOT NULL,
    DATE INTEGER NOT NULL,
    CONSTRAINT UNIQUE_NAME UNIQUE (NAME) ON CONFLICT REPLACE
)

但是，我只想在日期较新（数字较大）时才替换，或者在日期较旧时忽略新行。如何更改上面的SQL（用于SQLite）语句以实现这一目标？

Answer 1

不能使用表约束来执行此操作，而必须使用triggers：

CREATE INDEX just_some_index ON MyTable(Name);

CREATE TRIGGER MyTable_Name_insert_newer
BEFORE INSERT ON MyTable
FOR EACH ROW
WHEN (SELECT Date FROM MyTable WHERE Name = NEW.Name) <= NEW.Date
BEGIN
    DELETE FROM MyTable
    WHERE Name = NEW.Name;
END;

CREATE TRIGGER MyTable_Name_insert_older
BEFORE INSERT ON MyTable
FOR EACH ROW
WHEN (SELECT Date FROM MyTable WHERE Name = NEW.Name) > NEW.Date
BEGIN
    SELECT RAISE(IGNORE);
END;

（在SQLite中，没有结果的标量子查询仅返回NULL，因此插入新行会使两个WHEN子句均失败。）