SQL Server Syntax to Disable a Check Constraint when Executing a SQL Server Stored Procedure

Question

Just looking for the correct syntax to Disable a Check Constraint when Executing a SQL Server Stored Procedure. Is that possible on the EXEC or does the Disable actually have to occur on the UPDATE within the SQL Server Stored Procedure?

Thanks in advance for your review and am hopeful for a reply.

Thanks!

Answer 1

First of all, disabling constraints when running code is not really something to do lightly. I realise that there can be situations when you need to violate a constraint temporally during a transaction, and I might have run into these myself. I try to find workarounds like making the operations in a temp table and only update the target table in a final operation.

But if you need to do it, the best is to put it inside the stored procedure, and do inside a transaction so that you don't end up with the constraint disabled. Furthermore, if you do as Naomi suggested, the constraint will not be re-validated, which means that the optimizer cannot trust it, which can have performance implications.

This leads to:

   BEGIN TRANSACTION  
     
   ALTER TABLE tbl NOCHECK CONSTRAINT MyProblematicConstraint  
     
   \-- Do dirty stuff here  
     
   ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT MyProblematicConstraint  
     
   COMMIT TRANSACTION  

And yes, that should really be CHECK twice in a row there.

Answer 2

Hi @Bobby P
As experts answered above, you could disable a check constraint by Transact-SQL. See this article: Disable Check Constraints with INSERT and UPDATE Statements

What I want to emphasize is, if you want to re-enable a CHECK constraint that has previously been disabled, you should definitely make sure that you know what you’re doing. We can’t re-enable the constraint using WITH CHECK while we’ve got data in the table that violates the CHECK constraint. Either we need to update the data or we need to use WITH NOCHECK (or simply omit it altogether).

So we can successfully enable the constraint if we don’t check the existing data. Of course, in this case the CHECK constraint is not trusted. If we want the constraint to be trusted, we’ll need to update the data so that it doesn’t violate the constraint.

For more details, please refer to this article: What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

How you're currently calling the procedure? It can be done in the script
ALTER TABLE Purchasing.PurchaseOrderHeader
NOCHECK CONSTRAINT CK_PurchaseOrderHeader_Freight;
EXECUTE procedure
ALTER TABLE Purchasing.PurchaseOrderHeader
CHECK CONSTRAINT CK_PurchaseOrderHeader_Freight;