-- CREATE TEMP TABLE
CREATE TABLE T
(
Id INT NOT NULL,
IsEnabled bit not null,
NAME VARCHAR(500) NOT NULL
);
GO
-- CREATE FUNCTION
CREATE FUNCTION udfTemp( @Id int)
RETURNS INT
BEGIN
DECLARE @count INT;
SELECT @count = count(*)
FROM t
WHERE id = @id AND
isEnabled = 1
RETURN @count
END
GO
-- ADD CONSTRAINT TO THE TABLE
/*
Now this constraint ensure that there is
ONE AND ONLY ONE record where the is IsEnabled is true for a particular ID.
*/
ALTER TABLE t
WITH CHECK ADD CONSTRAINT ck_t
CHECK (dbo.udftemp(id)=(1))

-- Add some base data.
insert into t values (1,1,'item1')
insert into t values (2,1,'item2')
insert into t values (3,1,'item3')
insert into t values (4,1,'item4')
insert into t values (1,0,'item1 again')

SELECT * FROM T;

/*
Id IsEnabled NAME
----------- --------- ---------------
1 1 item1
2 1 item2
3 1 item3
4 1 item4
1 0 item1 again

(5 row(s) affected)
*/

-- Adding this will give us constraint violation
insert into t values (5,0,'item5') -- because Id 5 does not have a single record where IsEnabled is true
insert into t values (1,1,'item2 should fail') -- becuase Id 1 will have 2 records where IsEnabled is true

-- THE QUESTION?????????
SELECT * FROM T;

-- Why Does this pass?????
-- I am expecting the this should fail Because this update will leave the table in inconsistent state.
-- As shown in the below record set. ID 1 will not have any IsEnabled true
UPDATE T
SET IsEnabled = 0
WHERE Id = 1
AND IsEnabled = 1

SELECT * FROM T;
/*
Id IsEnabled NAME
----------- --------- -------------------
1 0 item1
2 1 item2
3 1 item3
4 1 item4
1 0 item1 again

*/


--------------------------------------------------------------------
-- CLEAN UP
--------------------------------------------------------------------

-- DROP TABLE T;