locked
Function check constraint ignored RRS feed

  • Question

  • I'm seeing a problem that I cannot find an explanation for. Is this a bug in SQL Server 2012? I'm trying to ensure that records in table S would FK only to "active" records in table M. Here is actual Transact-SQL code to replicate the problem:


    CREATE TABLE M
    (
    ID int NOT NULL PRIMARY KEY,
    IsActive bit NOT NULL ,
    )

    CREATE TABLE S
    (
    ID int NOT NULL PRIMARY KEY,
    MID int NOT NULL ,
    )

    ALTER TABLE S ADD CONSTRAINT S_FK FOREIGN KEY (MID) REFERENCES M(ID)
    GO


    CREATE FUNCTION [FN_S_If_Exists_References_Active_M] (
        @MID int
    )
    RETURNS BIT
    AS
    BEGIN

    IF NOT EXISTS ( SELECT MID FROM S WHERE MID = @MID )
       RETURN 1 -- Because we don't care if no S references some (active OR inactive) M

    IF EXISTS ( SELECT ID FROM M WHERE ID = @MID AND [IsActive] = 1 )
    RETURN 1
    RETURN 0

    END
    GO





    INSERT INTO M VALUES
    ( 1 , 0 )
    INSERT INTO M VALUES
    ( 2 , 0 )

    INSERT INTO S VALUES
    ( 100 , 2 )



    ALTER TABLE M WITH CHECK ADD CONSTRAINT [CK_S_References_Active_M_R_To_L] CHECK ( [dbo].[FN_S_If_Exists_References_Active_M](ID) = 1 )
    GO

    ALTER TABLE S WITH CHECK ADD CONSTRAINT [CK_S_References_Active_M_L_To_R] CHECK ( [dbo].[FN_S_If_Exists_References_Active_M](MID) = 1 )
    GO
    -- FAILS! GOOD! WE DO HAVE INCOMPATIBLE DATA. CONSTRAINT WORKS DURING ITS CREATION. GREAT.

    -- NOW FIX THE DATA
    UPDATE M SET IsActive = 1 WHERE ID = 2

    -- NOW REAPPLY THE CONSTRAINT
    ALTER TABLE S WITH CHECK ADD CONSTRAINT [CK_S_References_Active_M_L_To_R] CHECK ( [dbo].[FN_S_If_Exists_References_Active_M](MID) = 1 )
    GO
    -- WORKS NOW!

    -- SEE THE DATA
    SELECT _s.ID AS 'S ID', _m.ID AS 'M ID', _m.IsActive FROM S _s 
    RIGHT JOIN M _m ON
    _s.MID = _m.ID

    -- CHECK WHAT THE FUNCTION EVALUATES TO FOR MID = 2
    DECLARE @ret BIT
    EXEC @ret = [dbo].[FN_S_If_Exists_References_Active_M] @MID = 2
    PRINT @ret
    -- IT EVALS TO 1: GOOD

    -- NOW DO THE EVIL THING THAT SHOULD BE REJECTED BY THE CONSTRAINT: TRY TO CHANGE IsActive BACK TO 0
    UPDATE M SET IsActive = 0 WHERE ID = 2
    -- SUCCESS! VERY BAD, SQL SERVER!

    -- SEE THE DATA AGAIN
    SELECT _s.ID AS 'S ID', _m.ID AS 'M ID', _m.IsActive FROM S _s 
    RIGHT JOIN M _m ON
    _s.MID = _m.ID
    -- IT'S AS AWFUL AS SUSPECTED

    -- CHECK WHAT THE FUNCTION EVALUATES TO FOR MID = 2
    DECLARE @ret BIT
    EXEC @ret = [dbo].[FN_S_If_Exists_References_Active_M] @MID = 2
    PRINT @ret
    -- IT EVALS TO 0: GOOD (PROVING THE FUNCTION WORKS ALLRIGHT BUT IT'S THE CONSTRAINT THAT BLOWS)

    Dear Sirs, why is my constraint not working? Can a Microsoft person, among others, have a look at this, please?

    Thank you in advance for your wise suggestions,

    D.


    Tuesday, October 28, 2014 11:08 AM

Answers

  • First, using scalar UDFs that read the database in a CHECK constraint is not a good practice.  Triggers are simpler and more useful.

    This is the same behavior TiborK blogged about here: http://sqlblog.com/blogs/tibor_karaszi/archive/2009/12/17/be-careful-with-constraints-calling-udfs.aspx

    Your CHECK constraint is only fired for UPDATE of the columns referenced in the constraint definition.

    Your UPDATE only changes the column IsActive

    UPDATE M SET IsActive = 0 WHERE ID = 2 -- SUCCESS! VERY BAD, SQL SERVER!

    but your CHECK constraint is only defined on ID

    ALTER TABLE M WITH CHECK ADD CONSTRAINT [CK_S_References_Active_M_R_To_L]

    CHECK ( [dbo].[FN_S_If_Exists_References_Active_M](ID) = 1 )

    So this is simple and understandable behavior.  If you want the constraint checked on update of IsActive, you must use the column in the check constraint.  You don't have to really do anything with the value; just passing it into the function is enough.

    CREATE FUNCTION [FN_S_If_Exists_References_Active_M] (
         @MID int, @IsActive bit
     ) . . .

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, October 28, 2014 3:31 PM

All replies

  • Whats [DT_ID]? Is it a user defined datatype?

    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, October 28, 2014 11:32 AM
  • You modify the M table, but the constraint is created on the S table. SQL Server reckons that there is reason for "fire" the constraint since you don't even modify the table that the constraint is created on. This is expected. There are several reasons we want to be cautious with calling user functions in CHECK constraints. You have encountered one such reason. Here is a different (even more nasty) example:

    http://sqlblog.com/blogs/tibor_karaszi/archive/2009/12/17/be-careful-with-constraints-calling-udfs.aspx

    Not the answer you want, I know. Use triggers for these things. If SQL Server implemented ASSERTs in ANSI SQL, then you wouuld use those instead.


    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, October 28, 2014 11:38 AM
  • -- NOW REAPPLY THE CONSTRAINT
    ALTER TABLE S WITH CHECK ADD CONSTRAINT [CK_S_References_Active_M_L_To_R] CHECK ( [dbo].[FN_S_If_Exists_References_Active_M](MID) = 1 )
    GO
    ...

    -- NOW DO THE EVIL THING THAT SHOULD BE REJECTED BY THE CONSTRAINT: TRY TO CHANGE IsActive BACK TO 0
    UPDATE M SET IsActive = 0 WHERE ID = 2
    -- SUCCESS! VERY BAD, SQL SERVER!

    The constraint is defined on table S, but you update table M; a constraint don't work like trigger. If you want to get such a behaviour, then use a trigger instead.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, October 28, 2014 11:39 AM
  • My apologies, [DT_ID] really just stands for "int". I created this example based on our internal code, but apparently didn't clean everything internal out.
    Tuesday, October 28, 2014 11:52 AM
  • Olaf, Tibor,

    Thanks for your replies! It is indeed the intention to detect and reject changes to both tables that are incompatible with the function result. For this reason, I declare not one, but two constraints:

    CK_S_References_Active_M_R_To_L (i.e.: "right-to-left")

    and

    CK_S_References_Active_M_L_To_R (i.e.: "left-to-right")

    This way I hoped to trap edits to both tables. Note: the only time the constraints work corectly is when they are declared as part of "ALTER TABLE". After that they seem to be irrelevant...

    Does this change the way you see it at all?

    P.S. Tabor, you say: "SQL Server reckons that there is reason for "fire" the constraint". But that is actually what doesn't happen: the constraint is ignored after it has been applied.

    Thanks,

    D.

    Tuesday, October 28, 2014 12:00 PM
  • No, D., that doesn't change the way we see it. I fully understand what it is you are trying to achieve, and it isn't achievable using check constraints. Use triggers instead.

    Again, check the article I linked to, even more subtle = more difficult to understand. But this is the way the product work. The sooner we understand it, the sooner we can start implementing solutions that does what we want. :-)


    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, October 28, 2014 12:13 PM
  • Many thanks, Tibor!

    It seems that you've also dealt with constraint frustrations in your experience...

    I'll keep this thread alive for a bit longer, though, as I would like to get an official explanation from Microsoft about why this feature behaves the way it does, and why they don't consider it a bug.

    D.

    Tuesday, October 28, 2014 12:27 PM
  • Don't hold your hopes too high. ;-)

    The replies you've got so far is probably the most "official" you will get - I very much doubt somebody from the db engine team will see this thread (this is a peer-to-peer forum after all).

    You are much likelier to get a reply if you report this as a bug on connect.mictosoft.com. I suspect it will be replied to as "By design", but at least you might get some reply.

    As to *why* they don't fix it? My guesses are performance cost and developer/test resources (priorities).


    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, October 28, 2014 12:33 PM
  • I'll keep this thread alive for a bit longer, though, as I would like to get an official explanation from Microsoft about why this feature behaves the way it does, and why they don't consider it a bug.

    So you are asking why when you update table M, a constraint on table S does not fire?

    Before you call it a bug, do you have any documenation to back up your statement? Your expectations are not enough.

    If you like, you could call in a limitation. To implement this, SQL Server would have check if there are any functions bound to constraints on any table in the database that reference the table being updated. And then it would have to evaluate the constriant for all columns in those referening tables. If any of these tables is a 100-million row table, that would be extremely expensive. "But SQL Server could figure which rows that would be affected on check those". OK, sure, how would that be done? Or rather how many man-hours would it take to implement that?

    Overall, don't use functions with data access in constraints. This can be a real performance disaster. Cross-table checks for database integrity in triggers, should be done in triggers. And you need to implement it reciprocally.

    And, no, it is highly unlikely will not get any more official explanation from Microsoft in this forum.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, October 28, 2014 2:16 PM
  • Dear Erland,

    Thanks for your input. Please do note, though, that I declare not one, but two constraints -- one on each table -- to counter just the scenario you mention and give SQL Server some more benefit of the doubt.

    As for documentation, it is rather grey in this area: http://technet.microsoft.com/en-us/library/ms188258(v=sql.105).aspx At the very least, I hope that I will get MS to clarify for the masses what their implementation actually does or does not guarantee on row updates.

    I've filed a "bug report" at: https://connect.microsoft.com/SQLServer/feedback/details/1013526/function-check-constraint-ignored (the formatting, though appearing horrible there, looks allright once I log in and try to "edit" -- so MS folks should see things allright too).

    Regards,

    D.

    Tuesday, October 28, 2014 2:30 PM
  • First, using scalar UDFs that read the database in a CHECK constraint is not a good practice.  Triggers are simpler and more useful.

    This is the same behavior TiborK blogged about here: http://sqlblog.com/blogs/tibor_karaszi/archive/2009/12/17/be-careful-with-constraints-calling-udfs.aspx

    Your CHECK constraint is only fired for UPDATE of the columns referenced in the constraint definition.

    Your UPDATE only changes the column IsActive

    UPDATE M SET IsActive = 0 WHERE ID = 2 -- SUCCESS! VERY BAD, SQL SERVER!

    but your CHECK constraint is only defined on ID

    ALTER TABLE M WITH CHECK ADD CONSTRAINT [CK_S_References_Active_M_R_To_L]

    CHECK ( [dbo].[FN_S_If_Exists_References_Active_M](ID) = 1 )

    So this is simple and understandable behavior.  If you want the constraint checked on update of IsActive, you must use the column in the check constraint.  You don't have to really do anything with the value; just passing it into the function is enough.

    CREATE FUNCTION [FN_S_If_Exists_References_Active_M] (
         @MID int, @IsActive bit
     ) . . .

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, October 28, 2014 3:31 PM
  • David,

    You are an unsung genius! Many thanks. Mentioning the "IsActive" column in the constraint expression does the trick! I had just assumed SQL Server would watch changes to all the columns by default, as no documentation I've come across suggested anything else would be the case...

    Many thanks once again, David. Let this thread stick around for many years to come, radiating your advice to the programming community and immortalizing its pithy wisdom : )

    D.

    Tuesday, October 28, 2014 3:58 PM
  • That said, MS documentation... shall we say: "leaves much to be desired", to express it in non-abusive language : )

    D.

    Tuesday, October 28, 2014 4:00 PM
  • You could also get rid off the function at all and use a plain foreign key constraint by creating a unique index on [M] by (ID, IsActive), issuing a persisted calculated column on S as CAST(1 as bit) and using it in the FK constraint.

    SQL Server allows you to use a FK if the referenced table has a PK or a unique index by the columns involved in the reference.

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE M
    (
    ID int NOT NULL PRIMARY KEY,
    IsActive bit NOT NULL ,
    )
    GO
    CREATE UNIQUE NONCLUSTERED INDEX idx_uq_nc_M_ID_IsActive
    ON dbo.M(ID, IsActive);
    GO
    CREATE TABLE S
    (
    ID int NOT NULL PRIMARY KEY,
    MID int NOT NULL,
    IsActive AS CAST(1 AS bit) PERSISTED,
    CONSTRAINT S_FK_M FOREIGN KEY (MID, IsActive) REFERENCES M(ID, IsActive)
    )
    GO
    INSERT INTO M VALUES
    ( 1 , 0 )
    INSERT INTO M VALUES
    ( 2 , 1 );
    
    INSERT INTO S VALUES
    ( 100 , 2 );
    GO
    SELECT * FROM M;
    SELECT * FROM S;
    GO
    -- will fail
    UPDATE M 
    SET IsActive = 0 
    WHERE ID = 2;
    GO
    -- will fail
    INSERT INTO S(ID, MID)
    VALUES (200, 1);
    GO
    -- ok
    UPDATE M 
    SET IsActive = 1
    WHERE ID = 1;
    
    INSERT INTO S(ID, MID)
    VALUES (200, 1);
    GO
    SELECT * FROM M;
    SELECT * FROM S;
    GO
    DROP TABLE S, M;
    GO


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Tuesday, October 28, 2014 6:16 PM
  • Hi, Hunchback,

    Thanks for that idea as well. It's certainly also a solution; what makes me a bit uneasy about it, though, is the fact that I would need to pollute the definition of table S's columns... At least in the project I'm working on this won't be allowed : )

    Best regards,

    D.

    Wednesday, October 29, 2014 9:18 AM