USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
IF OBJECT_ID('t_uq') IS NOT NULL DROP FUNCTION t_uq
GO

CREATE TABLE t(c0 INT, c1 NVARCHAR(50), c2 bit)
GO

CREATE FUNCTION t_uq(@c1 NVARCHAR(50))
RETURNS bit
AS
BEGIN
 DECLARE @ret bit
 IF (SELECT COUNT(*) FROM t WHERE c1 = @c1 AND c2 = 1) > 1
   SET @ret = 0
 ELSE
   SET @ret = 1
 RETURN @ret
END
GO

ALTER TABLE t ADD CONSTRAINT t_c CHECK(dbo.t_uq(c1) = 1)

INSERT INTO t(c0, c1, c2) VALUES(1, 'a', 0) --OK
INSERT INTO t(c0, c1, c2) VALUES(2, 'a', 0) --OK
INSERT INTO t(c0, c1, c2) VALUES(3, 'b', 1) --OK
INSERT INTO t(c0, c1, c2) VALUES(4, 'b', 1) --Fails

--So far so good, but watch now:

UPDATE t SET c2 = 1 WHERE c0 = 2
--No error, the constraint doesn't do its job!

--We have invalid data:
SELECT * FROM t

— Permalink