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