How can we create a table constraint that limits a column to x number of identical values?

Question

In our retail system, we are planning to move basket information from the browser cookies to a table. We currently implement the basket limitations in the C# application but would like to move that business logic down to the database as constraints, if possible.

CREATE TABLE [dbo].[BasketProduct](
    [BasketProductId] [int] IDENTITY(1,1) NOT NULL,
    [BasketId] [int] NOT NULL,
    [ProductId] [int] NOT NULL,
    [Quantity] [int] NOT NULL,
 CONSTRAINT [PK_BasketProduct] PRIMARY KEY CLUSTERED 
([BasketProductId] ASC))

We want to restrict each baskets to have a maximum of 10 unique items but allow any quantities of each, so the BasketId value should only be present a maximum of 10 times in the table.

There doesn't seem to be an obvious constraint to do this as aggregates including window functions are not allowed in computed columns.

Can someone recommend an appropriate method?

Add what happens when the 10 unique items requirement is changed to 20? Or; yes 10 unique items, except for managers/superusers, they can have 30? I'd control this at the insert level via a "max allowed items" flag. — uberbloke, Dec 23 '19 at 13:58
We already compromised by not having 10 columns: one for each product. The constraint would be flexible enough that we could change without disruption to the system but we are happy with not having exceptions to the rule. — cloudsafe, Dec 23 '19 at 14:14
This seems like something the application layer should be handling, or you should handled in the SPs that control the `INSERT`/`DELETE`/`UPDATE`. A `CONSTRAINT` is the wrong place, as they handle rules for a specific row, not sub sets of the table. — Thom A, Dec 23 '19 at 14:14
I'd really not recommend trying to do it with just [DRI](https://stackoverflow.com/q/23510713/15498) — Damien_The_Unbeliever, Dec 23 '19 at 14:27
First, you should define the appropriate natural key - which is important for many reasons. An identity column as primary key will do nothing to prevent "duplicates". You also say "maximum of 10 unique items" - a term which is not well defined. If there were 12 rows with the same values for BasketId and ProductID, is that allowed or not? Wave your hands and say this will never happen - such assumptions will eventually be disproven. — SMor, Dec 23 '19 at 14:30
@SMor "the BasketId value should only be present a maximum of 10 times in the table." The constaint is just on BasketId with a max of 10 same values. — cloudsafe, Dec 23 '19 at 15:15
@Damien_The_Unbeliever That is interesting. I was looking along those lines but indexed view restrictions were confounding me. I really want this caught at the constraint level because there's a lot of development and applications. I can't trust all the apps to have the correct business logic. I'm not ruling that out! — cloudsafe, Dec 23 '19 at 15:18
As much as I'd hate to recommend it.... but what about.... ugh, I can't believe I'm about to say this.... what about a trigger..... ugh... I need to go wash my mouth out now. — TechGnome, Dec 23 '19 at 15:43
@SeanBrookins TechGnome It could work, but.. I don't like to, only using for auditing. — cloudsafe, Dec 23 '19 at 15:45
I don't like to do a lot of things, but if a stored procedure isn't going to work for you then the only other thing I can see making this work without drastically annoying the rest of us is going to be a trigger... and nobody likes triggers. — Sean Brookins, Dec 23 '19 at 16:10

uberbloke · Answer 1 · 2019-12-23T14:58:24.700

Please don't do this, functions in table definitions cause all sorts of problems...

but as an adjunct to my comment above, this will do what you want

But please don't do this

use tempdb;

DROP TABLE IF EXISTS CheckTbl
CREATE TABLE CheckTbl (col1 int);  
GO  
CREATE OR ALTER FUNCTION CheckCount(@tocheck int)  
RETURNS int  
AS   
BEGIN  
    DECLARE @return INTEGER = (select count(*) from CheckTbl where col1 = @tocheck);
    return @return;
END;  
GO  
ALTER TABLE CheckTbl  
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckCount(col1) <= 2 );  
GO  

INSERT INTO CheckTbl VALUES(1) -- allowed
INSERT INTO CheckTbl VALUES(2) -- allowed
INSERT INTO CheckTbl VALUES(1) -- allowed
INSERT INTO CheckTbl VALUES(2) -- allowed
INSERT INTO CheckTbl VALUES(1) -- NOT allowed

See also Custom function with check constraint SQL Server 2008 for a solution using an indexed view that be adaptable to this situation

That could be useful if the count was taken from from an aggregated indexed view instead of from the table. — cloudsafe, Dec 23 '19 at 15:42

Thom A · Answer 2 · 2019-12-23T14:34:46.710

This is less than ideal, but as I mentioned in the comment, I would handle this in the SP that performs the INSERT/UPDATE/DELETE (Unlikely a need for the DELETE). So, for the INSERT you would have something that looks like this:

USE Sandbox;
GO

CREATE TABLE [dbo].[BasketProduct](
    [BasketProductId] [int] IDENTITY(1,1) NOT NULL,
    [BasketId] [int] NOT NULL,
    [ProductId] [int] NOT NULL,
    [Quantity] [int] NOT NULL,
 CONSTRAINT [PK_BasketProduct] PRIMARY KEY CLUSTERED 
([BasketProductId] ASC));
GO

CREATE PROC AddBasketProduct @BasketID int, @ProductID int, @Quantity int AS
BEGIN

    DECLARE @DistinctProducts int;

    SELECT @DistinctProducts = COUNT(DISTINCT ProductID)
    FROM dbo.BasketProduct WITH (UPDLOCK) --As we need to control concurrency issues
    WHERE BasketId = @BasketID
      AND ProductID != @ProductID ;

    IF @DistinctProducts >= 10
        THROW 71245, N'Cannot have more than 10 different products in a single basket.',16; --Choose an error number and state appropraite for your applciation
    ELSE
        INSERT INTO dbo.BasketProduct (BasketId,
                                       ProductId,
                                       Quantity)
        VALUES(@BasketID,@ProductID,@Quantity);
END;

GO
--Make some sample data
INSERT INTO dbo.BasketProduct (BasketId,
                               ProductId,
                               Quantity)
VALUES(1,1,1),
      (1,2,1),
      (1,3,1),
      (1,4,1),
      (1,5,1),
      (1,6,1),
      (1,7,1),
      (1,8,1),
      (1,9,1),
      (1,10,1); --10 products.
GO
--11th product, will fail
EXEC dbo.AddBasketProduct @BasketID = 1,
                          @ProductID = 11,
                          @Quantity = 1;

GO

--Repetition of product 2, will work
EXEC dbo.AddBasketProduct @BasketID = 1,
                          @ProductID = 2,
                          @Quantity = 1;
GO

DROP PROC dbo.AddBasketProduct;
DROP TABLE dbo.BasketProduct;

DB<>Fiddle

That would work if we made some changes. There is no security model to stop developers from accessing the tables, so bypassing the sprocs (and they would, I'm sure). It could work if I locked it down and used an indexed view to hold basketid counts. With 10K+ users all adding items there could be a lot of count checks. — cloudsafe, Dec 23 '19 at 15:28

How can we create a table constraint that limits a column to x number of identical values?

2 Answers2