Restricting the Number of Rows in a SQL Table

When creating working examples for my development site, I frequently need to restrict the minimum and maximum number of rows that can be deleted from or inserted into a table by an anonymous user.

First, create a scalar function to return the current row count.¹

CREATE FUNCTION MyTableCount()
RETURNS INT
AS
BEGIN
	DECLARE @RowCount INT
	
	SELECT @RowCount = COUNT(*) MyTable
	RETURN @RowCount
END

Add a constraint to the table for the maximum number of rows.

ALTER TABLE MyTable
ADD CONSTRAINT Check_Row_Max 
CHECK ([dbo].[MyTableCount]()<=(20))

When a user attempts to insert the 21st row, the following error is returned.

A smilar table constraint cannot be used for the minimum number of rows², so I use an INSTEAD OF DELETE trigger.

CREATE TRIGGER dbo.Min_Row_Trigger
ON dbo.MyTable
INSTEAD OF DELETE
AS
BEGIN
     DECLARE @RowCount INT
     SELECT  @RowCount  =  dbo.MyTableCount()
     IF @RowCount > 15
     BEGIN
      DELETE T
      FROM DELETED D
      INNER JOIN dbo.MyTable T ON T.FirstName = D.FirstName AND T.LastName = D.LastName
     END
     ELSE
     BEGIN
       RAISERROR ('Minimum number of rows reached.', 11, 2)
     END
END

This examples assumes the primary key for MyTable is FirstName-LastName. When a delete is attempted and the table contains 15 rows an error is raised.

Otherwise, the row is explicitely deleted. You could change this up and use a ROLLBACK and a FOR DELETE trigger if you prefer.

---

1. Check constraints cannot use sub-queries like COUNT(*). See Support subqueries for CHECK CONSTRAINTs and the aricle in the next (2) reference.

2. “CHECK constraints are not validated during DELETE statements. Therefore, executing DELETE statements on tables with certain types of check constraints may produce unexpected results.” See CHECK Constraints from the MSDN library.