时间戳

首页 > 解决方案 > 我们如何创建一个将列限制为 x 个相同值的表约束？

sql-server - 我们如何创建一个将列限制为 x 个相同值的表约束？

问题描述

在我们的零售系统中，我们计划将购物篮信息从浏览器 cookie 转移到表格中。我们目前在 C# 应用程序中实现了篮子限制，但如果可能的话，希望将该业务逻辑作为约束下移到数据库中。

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

我们希望将每个篮子限制为最多包含 10 个唯一项目，但每个篮子允许任意数量，因此 BasketId 值最多只能在表中出现 10 次。

执行此操作似乎没有明显的约束，因为计算列中不允许包含窗口函数的聚合。

有人可以推荐一个合适的方法吗？

标签： sql-serverconstraintssql-server-2017

解决方案

这不太理想，但正如我在评论中提到的，我将在执行INSERT/ UPDATE/的 SP 中处理这个问题DELETE（不太可能需要DELETE）。因此，对于INSERT您来说，您将拥有如下所示的内容：

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<>小提琴

推荐阅读