4

Is this possible in SQLite?

Like:

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

What 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
Alex
  • 66,732
  • 177
  • 439
  • 641

3 Answers3

3

This is not supported directly; you have to 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;
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Depends on whether you have an index on (col1,col2,col3) … – CL. Aug 27 '13 at 13:08
  • @CL.: You answered just a day after SQLite added support for partial indexes, could you kindly look into my answer and tell me if that works? I'm still running an old version of SQLite here. – Alix Axel Sep 02 '13 at 00:54
2

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.

Alix Axel
  • 151,645
  • 95
  • 393
  • 500
  • [Download](http://www.sqlite.org/download.html) the command-line shell and try it out. (The documentation would already have told you what you did wrong.) – CL. Sep 02 '13 at 07:51
  • @CL.: Indeed, I overlooked the `LIKE` function. – Alix Axel Sep 02 '13 at 14:30
1

I'd suggest using table-level cheque constraint

ALTER TABLE T
ADD 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.

Artur Udod
  • 4,465
  • 1
  • 29
  • 58