如何在多租户数据库中设置唯一约束

这是一个多租户应用程序。所有记录都有一个客户 ID 来分隔客户数据。客户可以在此表中插入自己的数据并设置自己的唯一约束。每个客户都可以对 15 个字段中的任何一个字段设置唯一约束，也可以不设置任何约束。因此，在实际表上设置唯一约束是行不通的。

目前，为了检查是否应该插入记录，我们查询数据库以查看该记录是否存在。如果是，我们不插入，否则我们进行插入。如果在检查和插入之间插入重复记录，则重复记录将泄漏到数据库中。有没有办法保证不插入重复项？

正如评论中提到的，避免由于并行运行的进程之间的计时问题而插入重复项的一种方法是将行是否存在的测试与INSERT声明使用WHERE条款。 我建议动态 SQL 是一种可能的解决方案，但这里有一种使用位掩码的替代方法，如果客户端的约束设置存储在数据库中，该方法可能适合您。我做了一些假设，所以这可能对你没有帮助。

请注意，此代码被简化为仅使用三列（而不是 OP 中提到的十五列）。如果您决定将其生产化，最好将逻辑包装在存储过程中。

-- run this code for different values of @ClientId and @DataN to test the behaviour
DECLARE 
@ClientId int = 103, 
@Data1 int = 1, 
@Data2 int = 2,
@Data3 int = 3

DECLARE @clientConstraint TABLE (ClientId int PRIMARY KEY, Data1 bit, Data2 bit, Data3 bit)
DECLARE @clientData TABLE (Id int IDENTITY PRIMARY KEY, ClientId int, Data1 int, Data2 int, Data3 int)

-- set up four clients with different constraints for testing purposes
INSERT @clientConstraint (ClientId, Data1, Data2, Data3)
VALUES
(100,0,0,0),
(101,1,0,0),
(102,0,1,0),
(103,1,0,1)

-- set up an existing row in the data table for each client
INSERT @clientData (ClientId, Data1, Data2, Data3)
VALUES
(100,1,2,3),
(101,1,2,3),
(102,1,2,3),
(103,1,2,3)

-- build a bitmask of the client's unique columns
DECLARE @ClientConstraintMask bigint = 0
SELECT @ClientConstraintMask = Data1 + (Data2 * 2) + (Data3 * 4)
FROM @clientConstraint
WHERE ClientId = @ClientId

-- insert the data, building a uniqueness bitmask and comparing to client's settings
INSERT @clientData (ClientId, Data1, Data2, Data3)
SELECT @ClientId,@Data1, @Data2, @Data3
WHERE ( SELECT 
        CASE    WHEN    c1.Data1 = @Data1 
                THEN    @ClientConstraintMask & 1
                ELSE    0
        END +  
        CASE    WHEN    c1.Data2 = @Data2 
                THEN    @ClientConstraintMask & 2
                ELSE    0
        END +
        CASE    WHEN    c1.Data3 = @Data3 
                THEN    @ClientConstraintMask & 4
                ELSE    0
        END
        FROM    @clientData AS c1
        WHERE   c1.ClientId = @ClientId
    ) <> @ClientConstraintMask

-- view the results
SELECT * FROM @clientData

可能还值得一提的是，根据客户端数据量，您可能很难有效地索引客户端数据表以保持插入性能良好。如果索引位于最常用的唯一列集上，请考虑建立索引ClientId单独执行还不够好。