-- 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;
|