如何在多租户数据库中设置唯一约束 [英] How to set a unique constraint in a multi-tenant database

问题描述

这是一个多租户应用程序。所有记录都有一个客户端标识来分隔客户端数据。客户可以在此表中插入自己的数据，并设置自己的唯一约束。每个客户可以在15个字段中的任何一个或任何一个上设置唯一的约束。因此，对实际表设置唯一的约束将无法正常工作。

This is a muti-tenant app. All records have a client id to separate client data. Customers can insert their own data in this table and set their own unique constraints. Each customer can set a unique constraint on any of the 15 fields or none. So, setting a unique constraint on the actual table will not work.

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

Currently, to check if a record should be inserted or not we query the database to see if the record exists. If it does we dont insert otherwise we make the insert. If a duplicate record is inserted between the check and the insert then duplicates will leak into the database. Is there a way to guarantee duplicates don't get inserted?

推荐答案

正如评论中所提到的，一种避免重复的方法由于并行运行的进程之间的时序问题而插入的是将使用 WHERE INSERT 语句存在的测试结合在一起c $ c>子句。
我建议动态SQL是一个可能的解决方案，但下面是使用位掩码的替代方法，如果客户端的约束设置存储在数据库中，这可能适用于您。我已经做了一些假设，所以这可能不会帮助你。

As mentioned in the comments, one way to avoid duplicates being inserted because of timing issues between processes running in parallel would be to combine the test for whether a row exists with the INSERT statement using a WHERE clause. I suggested that dynamic SQL is one possible solution, but here's an alternative method using bitmasks which might work for you if the clients' constraint settings are stored in the database. I've made some assumptions, so this might not help you.

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

Note that this code is simplified to work with only three columns (rather than fifteen as mentioned in the OP). It would probably be best to wrap the logic in a stored procedure if you decide to productionise it.

-- 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 单独的索引执行不够好，请考虑对最常用的唯一的列集进行索引。

It's probably also worth mentioning that, depending on the volume of client data, you might struggle to effectively index the client data table to keep inserts performing well. Consider indexing on the most commonly used unique sets of columns if an index on ClientId alone doesn't perform well enough.

这篇关于如何在多租户数据库中设置唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！