sql - UNIQUE 约束，但仅当 column = 某物时

Question

这在 SQLite 中可能吗？

喜欢:

UNIQUE(col1, col2 = "something", col3),

我所说的独特是什么意思:

#1  col1=Foo | col2=Foo       | col3=Foo   > OK
#2  col1=Foo | col2=Foo       | col3=Foo   > OK
#3  col1=Foo | col2=something | col3=Foo   > OK
#4  col1=Foo | col2=something | col3=Foo   > Should fail because of #3
#5  col1=Foo | col2=something | col3=Bar   > OK
#6  col1=Foo | col2=something | col3=Bar   > Should fail because of #5

Answer 1

这不直接支持；你必须用触发器来实现它:

CREATE TRIGGER something_unique_check
BEFORE INSERT ON MyTable
FOR EACH ROW
WHEN NEW.col2 = 'something'
BEGIN
    SELECT RAISE(FAIL, '"something" record is not unique')
    FROM MyTable
    WHERE col1 = NEW.col1
      AND col2 = NEW.col2
      AND col3 = NEW.col3;
END;