Mssql: Update Statement Avoiding the Check Constraint

MSSQL: Update statement avoiding the CHECK constraint

There is a known issue where certain operations will lead to a check constraint that calls a UDF to be bypassed. The bug was listed on Connect (before it was scuttled and all the links were orphaned) and it has been acknowledged, but closed as Won't Fix. This means we need to rely on workarounds.

My first workaround would probably be an instead of update trigger. Thanks to Martin for keeping me honest and for making me test this further - I found that I did not protect against two rows being updated to 1 in the same statement. I've corrected the logic and added a transaction to help prevent a race condition:

CREATE TRIGGER dbo.CheckJobOwners ON dbo.JobOwners
INSTEAD OF UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRANSACTION;

  UPDATE j SET IsActive = 1 -- /* , other columns */
    FROM dbo.JobOwners AS j INNER JOIN inserted AS i
    ON i.LogID = j.LogID
    WHERE i.IsActive = 1 AND NOT EXISTS 
    (    -- since only one can be active, we don't need an expensive count:
      SELECT 1 FROM dbo.JobOwners AS j2
        WHERE j2.JPSID = i.JPSID
        AND j2.IsActive = 1 AND j2.LogID <> i.LogID
    )
    AND NOT EXISTS 
    (    -- also need to protect against two rows updated by same statement: 
      SELECT 1 FROM inserted AS i2
        WHERE i2.JPSID = i.JPSID
        AND i2.IsActive = 1 AND i2.LogID <> i.LogID
    );

  -- *if* you want to report errors:
  IF (@@ROWCOUNT <> (SELECT COUNT(*) FROM inserted WHERE IsActive = 1))
    RAISERROR('At least one row was not updated.', 11, 1);

  -- assume setting active = 0 always ok & that IsActive is not nullable
  UPDATE j SET IsActive = 0 -- /* , other columns */
    FROM dbo.JobOwners AS j INNER JOIN inserted AS i
    ON j.LogID = i.LogID
    WHERE i.IsActive = 0;

  COMMIT TRANSACTION;
END
GO

(My only reason for an instead of instead of after trigger is that you only update the rows you need to update, instead of having to rollback after the fact (which won't let you only rollback the invalid updates in the case of a multi-row update)).

There is a lot of good discussion about this issue here:

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

Check constraint with function fails on update although function returns correctly

Just don't - scalar functions are notorious efficiency problems. Your goal can be better (IMO) accomplished with a much simpler approach - a unique filtered index.

if object_id('dbo.CheckTable') is not null 
   drop table dbo.CheckTable;
go
CREATE TABLE [dbo].[CheckTable] (col1 int, col2 int, ok bit)
GO

create unique nonclustered index ixx on dbo.CheckTable(col2) where ok = 1;
go

-- all valid
insert dbo.CheckTable(col1, col2, ok) 
values (1, 1, 0), (2, 2, 1), (3, 0, 0);
go
-- still valid
insert dbo.CheckTable(col1, col2, ok) 
values (4, 1, 1);
go
-- not valid
insert dbo.CheckTable(col1, col2, ok) 
values (5, 1, 1);
go
-- not valid, dup in inserted batch
insert dbo.CheckTable(col1, col2, ok) 
values (6, 8, 1), (7, 8, 1);
go
-- valid
insert dbo.CheckTable(col1, col2, ok) 
values (9, 1, 0);
go

select * from dbo.CheckTable order by col2, ok, col1;
go

Check constraint that calls function does not work on update

Well well, I just learned something.

So it turns out that with CHECK CONSTRAINTS and UPDATES, the CONSTRAINT only gets checked if one of the columns referenced in the CONSTRAINT changed.

In your case, your CONSTRAINT is checking a UDF that you pass ItemID to.

In your UPDATE, presumably you are only changing the value of Allocation, and not ItemID, so the optimizer thinks "If ItemID didn't change, then there's no need to check the constraint", and it doesn't, and the UPDATE succeeds even though the CONSTRAINT should have failed it.

I tested this by rebuilding your function and Constraint and adding Allocation to it:

ALTER FUNCTION [dbo].[fn_AllocationIsValid] (@itemId as int, @Allocation int)  
RETURNS int  AS  
BEGIN 
DECLARE @isValid bit;

SELECT @isValid = CASE WHEN ISNULL(SUM(Allocation), 0) <= MAX(Inventory) THEN 1 ELSE 0 END
FROM Allocations A 
JOIN Items I ON I.Id = A.ItemId
WHERE I.Id = @itemId
GROUP BY I.Id;

RETURN @isValid;
END

And:

ALTER TABLE [dbo].[Allocations]  WITH CHECK ADD  CONSTRAINT [CK_Allocations] 
CHECK  (([dbo].[fn_AllocationIsValid]([Itemid], Allocation)=(1)))
GO

Note that I had to DROP the original constraint first and truncate/repopulate the table, but that's nothing that you need me to show you how to do.

Also note that Allocation isn't involved in any of the logic of the function. I didn't change the logic at all, I just added a parameter for @Allocation. The parameter never gets used.

Then when I did an UPDATE that raised the SUM of Allocation to above the MAX, I got the expected error:

The UPDATE statement conflicted with the CHECK constraint
"CK_Allocations". The conflict occurred in database "Tab_Test", table
"dbo.Allocations".

Why? Because even though @Allocation isn't used in the logic of the function, the Allocation column is referenced in the CONSTRAINT, so the optimizer does check the constraint when the value of Allocation changes.

Some have argued that because of things like this, it's always preferable to use a TRIGGER instead of a CHECK CONSTRAINT that calls a UDF. I'm not convinced, and I haven't seen any reproducible experiments that prove it. But I leave it up to you which way you want to go with this.

Hopefully this information will prove useful to some future readers.

PS: Ascribing proper credit, I learned all this with some help from the forum post in my comment on the question, which led to this blog on the subject.

SQL Update conflict with Check constraint error

Take a record with the following values for example:

• SubTotal: 10
• GST: 0.5
• Total: 10.5

Now you execute your Update with an @extCost of 2

SET 
  SubTotal = SubTotal + @ExtCost, 
  GST = SubTotal * 0.05, 
  Total = SubTotal + GST
WHERE JobNumber = @JobNumber

During an update, the current values are taken to fill in the column values, so this is the same as

SET 
  SubTotal = 10 + 2, 
  GST = 10 * 0.05, 
  Total = 10 + 0.05
WHERE JobNumber = @JobNumber

So (subtotal) 12 > (total) 10.05 resulting in a constraint issue.

What you want is this to take the calculated values into account, so you need to do the calculations per field.

SET 
  SubTotal = SubTotal + @ExtCost, 
  GST = (SubTotal + @ExtCost) * 0.05, 
  Total = (SubTotal + @ExtCost) + ((SubTotal + @ExtCost) * 0.05)
WHERE JobNumber = @JobNumber

or shorter

SET 
  SubTotal = SubTotal + @ExtCost, 
  GST = (SubTotal + @ExtCost) * 0.05, 
  Total = (SubTotal + @ExtCost) * 1.05
WHERE JobNumber = @JobNumber

Sql error on update : The UPDATE statement conflicted with the FOREIGN KEY constraint

This error is encountered when the primary key of a table is updated but it is referenced by a foreign key from another table and the update specific is set to No action. The No action is the default option.

If this is your case and No action is set on the update operation you can change the foreign-key definition to Cascade.

Right click your foreign key and select Modify. In the Foreign key relationships dialog under the INSERT and UPDATE specifics set the UPDATE rule on Cascade:

You can also set the rule using T-SQL:

ALTER TABLE YourTable
DROP Constraint Your_FK
GO

ALTER TABLE YourTable
ADD CONSTRAINT [New_FK_Constraint]
FOREIGN KEY (YourColumn) REFERENCES ReferencedTable(YourColumn)
ON DELETE CASCADE ON UPDATE CASCADE
GO 

Hope this helps

Adding a constraint to prevent duplicates in SQL Update Trigger

You can add a unique contraint on the table, this will raise an error if you try and insert or update and create duplicates

ALTER TABLE [Users] ADD  CONSTRAINT [IX_UniqueUserEmail] UNIQUE NONCLUSTERED 
(
    [Email] ASC
)

ALTER TABLE [Users] ADD  CONSTRAINT [IX_UniqueUserName] UNIQUE NONCLUSTERED 
(
    [UserName] ASC
)

EDIT: Ok, i've just read your comments to another post and seen that you're using NVARCHAR(MAX) as your data type. Is there a reason why you might want more than 4000 characters for an email address or username? This is where your problem lies. If you reduce this to NVARCHAR(250) or thereabouts then you can use a unique index.

---

Related Topics