Deletes ignore check contraints

Deletes Ignore Check Constraints

I know it sounds a bit odd but DELETE statements really do ignore table constraints. Running the code below in a test database will setup a test to prove this.

CREATE TABLE ProductConstraintTest
(
  ProductID	INT IDENTITY(1,1),
  ProductCode	VARCHAR(50),
  ProductName	VARCHAR(100),
  Cost		DECIMAL(19,4),
  Price		DECIMAL(19,4)
);
GO

CREATE FUNCTION chkProductCount()
RETURNS INT
AS 
BEGIN
   DECLARE @Result INT
   SELECT @Result = COUNT(*) FROM ProductConstraintTest
   RETURN @Result
END;
GO

ALTER TABLE ProductConstraintTest
ADD CONSTRAINT chk_MinProdCount CHECK (dbo.chkProductCount() >= 1);
GO

This will create a table named ProductConstraintTest that has a constraint named chk_MinProdCount. This constraint uses the function chkProductCount to return the row count of ProductConstraintTest. The idea of the constraint is to ensure this row count never dips below 1.

Now, let’s insert some test data.

INSERT	ProductConstraintTest(ProductCode, ProductName, Cost, Price)

SELECT	*
FROM	(	
      VALUES	('0001', 'Log Splitter 500', 45.87, 129.99),
          ('0002', 'Lawn mower', 60.38, 189.99),
          ('0003', 'Hedge Trimmer', 23.19, 59.99),
          ('0004', 'Rake', 15.00, 28.99),
          ('0005', 'Bucket', 4.33, 9.99) 
    ) 
    AS Products(ProductCode, ProductName, Cost, Price);
GO

To test our constraint we’ll use the function to return the row count, then we’ll empty the table and finally we’ll use the function again to check the row count after the DELETE.

SELECT dbo.chkProductCount() 

DELETE FROM ProductConstraintTest

SELECT dbo.chkProductCount()

The code above returns the row count before and after the DELETE.

Delete statement ignoring the check constraint

No error from our check constraint!

So, how could we enforce a minimum row count? One option is to use a trigger like the one below.

CREATE TRIGGER DeleteChkMinRowCount ON dbo.ProductConstraintTest 
AFTER DELETE
AS
BEGIN
  IF ((SELECT dbo.chkProductCount()) < 1)
  BEGIN
    RAISERROR('DELETE failed. Minimum row count is 1.', 16, 1);
    ROLLBACK;
  END
END
GO

This will fire after a DELETE statement runs against our table. If the DELETE brings the row count of the table under our desired threshold, it throws an error which causes the DELETE to be rolled back.

Running the same DELETE as before, with the trigger in place, only returns the first row count check and then our error.

Trigger rolls back transactions

 

Trigger used to enforce a check constraint

 

Having a unit test in this project, to ensure our constraint actually enforced the data as we expected, would have caught this issue early on. tSQLt is an opensource framework designed for creating unit tests for T-SQL that can help catch unexpected issues like this.

Like most of my posts the example here is fairly simple but I hope this shows the importance of testing your schema and not just your code.

About the author

James Anderson

I'm a freelance SQL Server DBA in Southampton, UK. Performance tuning code and improving development processes are what I love to do for work. Outside of work, I live to travel to as many places as possible with my wife Sarah.

11 thoughts on “Deletes Ignore Check Constraints”

  1. That’s really cool. Although I’ve always thought that constraints (including check constraints) are always evaluated one row at a time. So they’re really row constraints rather than table constraints.

    Your example tries to be a table constraint by using a function. This leads to some other uncomfortable questions we can ask:

    If the check were on the table, then it should fail. if the check is only on a row, then … what row?

    If the row is not there, does it violate the check? (If a tree falls and no one is around does it make a sound?)

    When we first build the empty table, should we be able to add the check on it? (No row violates the check)

    If we were to change the constraint to <= 5 rows and there were ten rows, which rows fail the check?

    Very cool example, how did you stumble on it?

  2. Thanks Michael. You raise some interesting questions there. I believe the constraint should fail. I expect the reason it doesn’t is because constraints are usually something like; Make sure cost is > 0 or date of birth is < GETDATE(). In these cases there is no need to consult constraints when deleting data. It seems to me that this is a gap in using constraints. I do think it should be valid to add this constraint to an empty table because otherwise we would have to dynamically add it after some data had been inserted. I found it here on BOL https://technet.microsoft.com/en-us/library/ms188258(v=sql.105).aspx

    1. ” I do think it should be valid to add this constraint to an empty table because otherwise we would have to dynamically add it after some data had been inserted” So the constraint should be enforced sometimes but not all times ?

      1. Hi Ray, Is a constraint really enforcing anything on a table with 0 rows? If it wasn’t possible to add the constraint on an empty table we would need something to automatically add it once the data had been inserted. If we had something like that would it really be any different to having a constraint on an empty table? I think Michael Swart put it best in the comment above “If the row is not there, does it violate the check? (If a tree falls and no one is around does it make a sound?)”

  3. Thanks James,
    Here’s another puzzle: I changed your example above to >=2. I can’t seem to add any rows. I guess SQL Server can’t get to two rows without going through one row.

    1. Yeah I found the same thing. It would make more sense to me if constraints worked at the statement level. 1 statement (INSERT, UPDATE or DELETE) should trigger the check 1 time once it’s complete. Maybe there is a reason it doesn’t work like that that I don’t know about.

      1. I think that the reason is performance: rollback is resource expensive, therefore it’s good to fail as soon as possible. Moreover, check constraints are intended for row-level integrity, not for table-level integrity.

    1. Hi Chris. Agreed and that’s the link I shared in the comments above. I was surprised when I read that so I tested it and wrote this post for those who hadn’t.

Leave a Reply

Your email address will not be published. Required fields are marked *

Sign Up

James Anderson SQL Server DBA

Hi, I'm James Anderson. When I'm not blogging about SQL Server, I do this.

The SQL People

DBA Stackexchange Profile