UNIQUE constraint but only when column = something
This is not supported directly; you have to implement it with a trigger:
CREATE TRIGGER something_unique_checkBEFORE INSERT ON MyTableFOR EACH ROWWHEN 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;
I haven't tested it but I think you can do that with partial indexes in SQLite 3.8.0 (released 2013-08-26):
CREATE UNIQUE INDEX "partial_index" ON "table" ("col1", "col2", "col3")WHERE ("col2" = 'something');I might be wrong though.
I'd suggest using table-level cheque constraint
ALTER TABLE TADD CONSTRAINT CK_something CHECK (col2 != "something" OR (col1<>col2 AND col1<>col3 AND col2<>col3))other option is to use trigger, but that's a more sophisticated approach.