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