SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

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

The Database Avenger

My name is James Anderson and I am a SQL Server production DBA in the south of the UK. I have been working with databases since 2008. The first RDBMS I worked on was Paradox and I never want to see it again. I quickly moved to using T-SQL in SQL Server 2005. I have worked on every version of the product since. I enjoy query tuning and server optimisation. I detest transactional replication.

Comments

Leave a comment on the original post [thedatabaseavenger.com, opens in a new window]

Loading comments...