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
(
  ProductIDINT IDENTITY(1,1),
  ProductCodeVARCHAR(50),
  ProductNameVARCHAR(100),
  CostDECIMAL(19,4),
  PriceDECIMAL(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.

INSERTProductConstraintTest(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.

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.

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.

The post Deletes Ignore Check Constraints appeared first on The Database Avenger.