I can write a constraint:
ALTER TABLE [Table1] ADD CONSTRAINT [Lalala10] UNIQUE (Column1, Column2)
How would I say that this constraint needs to be applied only when Column2 = 'BLAH'? I.e. uniqueness is enforced only in the case (Column1 = 'ANYTHING', Column2 = 'BLAH'), so there can be multiple rows with ('ANYTHING', 'POO'), ('WHATEVER', 'FOO') but not with ('ANYTHING', 'BLAH'), ('WHATEVER', 'BLAH').
I tried this, but it does not work as intended (it acts as if the CHECK bit is not present):
ALTER TABLE [Table1] ADD CONSTRAINT [Lalala10] UNIQUE (Column1, Column2), CHECK (Column2 = 'BLAH')
I am interested if this is possible as a CONSTRAINT. Not interested using CREATE UNIQUE NONCLUSTERED INDEX (that I can do already).
Thanks!