en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
אוק17

Written by: ronen ariely
17/10/2014 10:27 RssIcon

Sometimes A new version or feature release and a new bug which related to this feature revealed, but sometimes we stuck with a BUG for years.. many years... 15 years even... will it be fix in the next version?

Using User Defined Function In Constraint can lead to a BUG! In this short blog I will just reproduce the BUG and show a simple workaround. This is not a new topic, and you probably can find lot of articles on net,on the same issue, but since it came up again... i decided to post this blog again.


/************** BUG usin user function in constraint  */
 
create table T (Customer INT, IsDefault INT DEFAULT 1)
GO
 
;CREATE FUNCTION MyTConstraint_fn_Error (
    @_Customer INT
)
returns int
as begin
    declare @R int = 0
    select @R = SUM(IsDefault) from T where Customer = @_Customer
    --print @R
    return @R
end
GO
 
-- This will bring error!
ALTER TABLE T
    with check
    ADD CONSTRAINT CIX
    CHECK (dbo.MyTConstraint_fn_Error(Customer) = 1)
GO
 
/***************** Checking time! */
insert T (Customer) values (1)
select * from T
GO -- OK, since we have default value 1, it fit the constraint
 
TRUNCATE TABLE T
insert T (Customer,IsDefault) values (1,1)
select * from T
GO -- OK, it fit the constraint
 
insert T (Customer,IsDefault) values (1,1)
GO -- The statement has been terminated. since it is not fit the constrant, this is OK
 
insert T (Customer,IsDefault) values (2,0)
GO
-- The statement has been terminated. since it is not fit the constrant, this is OK
 
insert T (Customer,IsDefault) values (2,1)
select * from T
GO -- OK, it fit the constraint
 
-->> look insert is working ok... time to move to update and get BUGy result
update T set IsDefault = 0 where Customer = 1
GO
-- If we use only single column in the function we get OK here! this is a bug!
-- This update is not fit with the constraint!
 
/*************************** short explantion: */
-- This problem has been there since the 2000 days!!!
 
-- If we use only single column as input in the function, then constrant is not trigger on updates of other columns
-- in our example the input was the column: Customer
-- but since our constraint include UDF which use information from other column as well, we get a BUG
 
-- We can check that the constraint is a column's constrain:
SELECT parent_column_id,*
FROM sys.check_constraints
WHERE parent_object_id  = object_id('t') and [name] = 'CIX' and [type] = 'C';
GO
-- 0 indicates a table-level CHECK constraint.
-- Non-zero value indicates that this is a column-level CHECK constraint defined on the column with the specified ID value.
 
/****************************** Work around */
-- Adding another column to the constraint definition (to the function input) will change the behavior, and it will work OK.
 
-- drop bad constraint and clear table
TRUNCATE TABLE T
ALTER TABLE T DROP CONSTRAINT CIX;
GO
 
-- Create WorkArounf function
;CREATE FUNCTION MyTConstraint_fn_Good (
    @_Customer INT
    , @_IsDefault INT-- I dont need this but there s a bug if we use one column
)
returns int
as begin
    declare @R int = 0
    select @R = SUM(IsDefault) from T where Customer = @_Customer
    --print @R
    return @R
end
GO
 
-- using good constraint
ALTER TABLE T
    with check
    ADD CONSTRAINT CIX
    CHECK (dbo.MyTConstraint_fn_Good(Customer, IsDefault) = 1)
GO
 
/***************** Checking time! */
insert T (Customer) values (1)
select * from T
GO -- OK, since we have default value 1, it fit the constraint
 
TRUNCATE TABLE T
insert T (Customer,IsDefault) values (1,1)
select * from T
GO -- OK, it fit the constraint
 
insert T (Customer,IsDefault) values (1,1)
GO -- The statement has been terminated. since it is not fit the constrant, this is OK
 
insert T (Customer,IsDefault) values (2,0)
GO
-- The statement has been terminated. since it is not fit the constrant, this is OK
 
insert T (Customer,IsDefault) values (2,1)
select * from T
GO -- OK, it fit the constraint
 
-->> look insert is working ok... time to move to update and get BUGy result
update T set IsDefault = 0 where Customer = 1
GO
-- The statement has been terminated. since it is not fit the constrant, this is OK
/************ We won the BUG :-)  *************/
 
 
-- We can confirm the differences now.
-- This constraint is a table-level CHECK constraint.
SELECT parent_column_id,*
FROM sys.check_constraints
WHERE parent_object_id  = object_id('t') and [name] = 'CIX' and [type] = 'C';
GO
 
 
 
 
DROP table T
DROP FUNCTION MyTConstraint_fn_Good
DROP FUNCTION MyTConstraint_fn_Error
GO

Have fun :-)

Next I will add here my propused solution (Changing all constraint to table-level CHECK constraint will reduce performance probably and this is not my solution idea).