UNIQUE restriction, but only when column = something

Question

Is this possible in SQLite?

how

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

What do I mean by unique:

 #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

This is not directly supported; you must implement it with a trigger:

 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; 

Answer 2

I have not tested it, but I think you can do it 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'); 

Maybe I'm wrong.

Answer 3

I would suggest using a table level cheque constraint

 ALTER TABLE T ADD CONSTRAINT CK_something CHECK (col2 != "something" OR (col1<>col2 AND col1<>col3 AND col2<>col3)) 

another option is to use a trigger, but this is a more complex approach.