CODEWITHSUNDEEP
sqlsqlitepdoconstraintsunique
Alex
Alex

Reputation: 68044

UNIQUE constraint but only when column = something

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

Upvotes: 4

Views: 72

Answers (3)

Alix Axel
Alix Axel

Reputation: 154563

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.

Upvotes: 2

CL.
CL.

Reputation: 180080

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;

Upvotes: 3

Artur Udod
Artur Udod

Reputation: 4743

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.

Upvotes: 1

Related Questions