TSQL：根据自定义标识创建自定义标识？ （管理数据库修订）

Question

我想根据自定义身份创建自定义身份。或者可能是类似于身份的东西，其功能类似于自动递增密钥。

例如，如果我有一张图纸的主键，我希望它的修订基于图纸编号。

示例

DRAWING
ID    | REV   | INFO
------+-------+------
1     | 0     | "Draw1"
2     | 0     | "Draw2"
2     | 1     | "Draw2Edit"
2     | 2     | "Draw2MoreEdit"
3     | 0     | "Draw3"
4     | 0     | "Draw4"

如果我要在表中插入更多记录，例如：

INSERT INTO DRAWING (INFO) VALUES ("Draw5")
INSERT INTO DRAWING (ID,INFO) VALUES (3,"Draw3Edit")

我的表需要：

DRAWING
ID    | REV   | INFO
------+-------+------
1     | 0     | "Draw1"
2     | 0     | "Draw2"
2     | 1     | "Draw2Edit"
2     | 2     | "Draw2MoreEdit"
3     | 0     | "Draw3"
3     | 1     | "Draw3Edit"      --NEW ROW
4     | 0     | "Draw4"
5     | 0     | "Draw5"          --NEW ROW

T-SQL

CREATE TABLE DRAWING
(
    ID INT,
    REV INT,  
    INFO VARCHAR(50),
    PRIMARY KEY (ID,REV)
);

CREATE TABLE CURRENT_DRAWING
(
    ID INT IDENTITY (1,1),
    DRAWING_ID INT,
    DRAWING_REV INT,
    PRIMARY KEY (ID),
    FOREIGN KEY (DRAWING_ID,DRAWING_REV) REFERENCES DRAWING (ID,REV)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

我正在使用 SQL Server Management Studio 2005< /strong> 并使用 SQL Server 2000 数据库。

我也会接受可能的替代方案。主要目标是让新绘图的 ID 自动递增。 ID 将保持不变，REV 将在新的图纸修订中增加。

更新：

我认为我已经接近我想要的了：

DROP TABLE DRAW

GO

CREATE TABLE DRAW
(
    ID INT DEFAULT(0), 
    REV INT DEFAULT(-1), 
    INFO VARCHAR(10), 
    PRIMARY KEY(ID, REV)
)

GO

CREATE TRIGGER TRIG_DRAW ON DRAW
FOR INSERT
AS
BEGIN
    DECLARE @newId INT,
            @newRev INT,
            @insId INT,
            @insRev INT

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    BEGIN TRANSACTION

    SELECT @insId = ID FROM inserted
    SELECT @insRev = REV FROM inserted

    PRINT 'BEGIN TRIG'
    PRINT @insId
    PRINT @insRev
    PRINT @newId
    PRINT @newRev


    --IF ID=0 THEN IT IS A NEW ID
    IF @insId <=0
    BEGIN
        --NEW DRAWING ID=MAX+1 AND REV=0
        SELECT @newId = COALESCE(MAX(ID), 0) + 1 FROM DRAW
        SELECT @newRev = 0
    END
    ELSE
    --ELSE IT IS A NEW REV
    BEGIN
        --CHECK TO ENSURE ID EXISTS
        IF EXISTS(SELECT * FROM DRAW WHERE ID=@insId AND REV=0)
        BEGIN
            PRINT 'EXISTS'
            SELECT @newId = @insId
            SELECT @newRev = MAX(REV) + 1 FROM DRAW WHERE ID=@insID
        END
        ELSE
        --ID DOES NOT EXIST THEREFORE NO REVISION
        BEGIN
            RAISERROR 50000 'ID DOES NOT EXIST.'
            ROLLBACK TRANSACTION
            GOTO END_TRIG
        END
    END

    PRINT 'END TRIG'
    PRINT @insId
    PRINT @insRev
    PRINT @newId
    PRINT @newRev

    SELECT * FROM DRAW

    UPDATE DRAW SET ID=@newId, REV=@newRev WHERE ID=@insId



    COMMIT TRANSACTION
    END_TRIG:
END

GO


INSERT INTO DRAW (INFO) VALUES ('DRAW1')
INSERT INTO DRAW (INFO) VALUES ('DRAW2')
INSERT INTO DRAW (ID,INFO) VALUES (2,'DRAW2EDIT1') --PROBLEM HERE
INSERT INTO DRAW (ID,INFO) VALUES (2,'DRAW2EDIT2')
INSERT INTO DRAW (INFO) VALUES ('DRAW3')
INSERT INTO DRAW (INFO) VALUES ('DRAW4')

GO

--SHOULD THROW
INSERT INTO DRAW (ID,INFO) VALUES (9,'DRAW9')

GO

SELECT * FROM DRAW

GO

但是，我不断收到违反主键约束。

我已经放置了调试语句，看来我不太可能违反我的主键：

BEGIN TRIG
0
-1


END TRIG
0
-1
1
0

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
BEGIN TRIG
0
-1


END TRIG
0
-1
2
0

(2 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
BEGIN TRIG
2
-1


EXISTS
END TRIG
2
-1
2
1

(3 row(s) affected)
Msg 2627, Level 14, State 1, Procedure TRIG_DRAW, Line 58
Violation of PRIMARY KEY constraint 'PK__DRAW__56D3D912'. Cannot insert duplicate key in object 'DRAW'.
The statement has been terminated.

它

ID  | REV    | INFO
----+--------+------------
1   |   0    |  DRAW1
2   |  -1    |  DRAW2EDIT1  --This row is being updated to 2 1 
2   |   0    |  DRAW2

在失败之前打印，并且第 2 -1 行正在更新为 2 1。它不应该违反我的主键。

Answer 1

我实际上会推荐一种替代数据设计。这种键和序列模式很难在关系数据库中正确实现，而且弊大于利。

您有很多选择，但最简单的选择是从将表分成两部分开始：

CREATE TABLE DRAWING
(
    ID INT IDENTITY(1, 1),
    PRIMARY KEY (ID)
);

CREATE TABLE DRAWING_REVISION
(    
    ID INT IDENTITY(1, 1),
    DRAWING_ID INT,
    INFO VARCHAR(50),
    PRIMARY KEY (ID),
    CONSTRAINT FK_DRAWING_REVISION_DRAWING FOREIGN KEY (DRAWING_ID) REFERENCES DRAWING(ID)
);

这样做的好处是可以准确地表示数据并正确工作，而无需您付出额外的努力。当您想要向工程图添加新修订时，只需向 DRAWING_REVISION 表添加一行即可。由于主键使用 IDENTITY 规范，因此您不必执行查找下一个 ID 的工作。

明显的解决方案及其缺点

如果您需要一个人类可读的修订号，而不是仅用于您的服务器的ID，则可以通过两种方式完成。它们首先将 REV INT 添加到 DRAWING_REVISION 的数据定义中，并添加 CONSTRAINT UK_DRAWING_REVISION_DRAWING_ID_REV UNIQUE (DRAWING_ID, REV)。当然，诀窍是找出给定绘图的下一个修订号。

如果您希望每个用户只有很少数量的并发用户，则只需在您的应用程序代码或 >INSTEAD OF INSERT 触发器。不过，如果并发量较高或运气不好，用户最终可能会互相阻塞，因为他们可能会尝试将 DRAWING_ID 和 REV 的相同组合插入 DRAWING_REVISION< /代码>。

一些背景

这个问题实际上只有一种解决方案，但解释为什么只有一种解决方案需要一点背景信息。考虑以下代码：

BEGIN TRAN

INSERT DRAWING DEFAULT VALUES;
INSERT DRAWING DEFAULT VALUES;
SELECT ID FROM DRAWING; -- Output: 1, 2

ROLLBACK TRAN

BEGIN TRAN

INSERT DRAWING DEFAULT VALUES;
SELECT ID FROM DRAWING; -- Output: 3

ROLLBACK TRAN

当然，后续执行的输出会有所不同。在幕后，SQL Server 正在分发 IDENTITY 值并递增计数器。如果您从未实际提交该值，则服务器不会尝试“回填”序列中的漏洞 - 这些值是在仅向前的基础上提供的。

这是一个功能，而不是一个错误。 IDENTITY 列被设计为有序且唯一的，但不一定是紧密排列的。保证紧密打包的唯一方法是序列化所有传入请求，确保每个请求在下一个请求开始之前完成或终止；否则，服务器可能会尝试回填半小时前发出的 IDENTITY 值，但结果却是一个长时间运行的事务（即该 IDENTITY 的初始接收者） code> value) 提交具有重复主键的行。

（值得指出的是，当我说“事务”时，并不需要引用 TSQL TRANSACTION，尽管我会推荐使用它们。它绝对可以是应用程序或 SQL 上的任何过程服务器端可能需要任意时间，即使该时间只是SELECT下一个修订号以及此后立即INSERT新DRAWING_REVISION所需的时间。）

这种回填值的尝试只是变相的序列化，因为在同时有两个 INSERT 请求的情况下，它会惩罚第二个提交的请求。这迫使最后到达的人再次尝试（可能多次，直到碰巧不存在冲突）。一次有一个成功的提交：序列化，尽管没有队列的好处。

SELECT MAX(REV) + 1 方法也有同样的缺点。当然，MAX 方法不会尝试回填值，但它确实会强制每个并发请求争夺相同的修订号，并获得相同的结果。

为什么这样不好？数据库系统是为并行性和通用性而设计的：这种能力是托管数据库相对于平面文件格式的主要优势之一。

假装正确

那么，经过所有冗长的阐述之后，你能做些什么来解决这个问题呢？您可以祈祷自己永远不会看到太多并发用户，但为什么您不希望自己的应用程序得到广泛使用呢？毕竟，你不希望成功成为你的失败。

解决方案是像 SQL Server 对 IDENTITY 列所做的那样：将它们分配出去，然后将它们扔到一边。您可以使用类似以下 SQL 代码的内容，或者使用等效的应用程序代码：

ALTER TABLE DRAWING ADD REV INT NOT NULL DEFAULT(0);

GO

CREATE PROCEDURE GET_REVISION_NUMBER (@DRAWING_ID INT) AS
BEGIN
    DECLARE @ATTEMPTS INT;
    SET @ATTEMPTS = 0;
    DECLARE @ATTEMPT_LIMIT INT;
    SET @ATTEMPT_LIMIT = 5;
    DECLARE @CURRENT_REV INT;
    LOOP:
        SET @CURRENT_REV = (SELECT REV FROM DRAWING WHERE DRAWING.ID = @DRAWING_ID);
        UPDATE DRAWING SET REV = @CURRENT_REV + 1 WHERE DRAWING.ID = @DRAWING_ID AND REV = @CURRENT_REV;
        SET @ATTEMPTS = @ATTEMPTS + 1;
        IF (@@ROWCOUNT = 0)
        BEGIN
            IF (@ATTEMPTS >= @ATTEMPT_LIMIT) RETURN NULL;
            GOTO LOOP;
        END
    RETURN @CURRENT_REV + 1;
END

@@ROWCOUNT 检查非常重要 – 该过程需要是非事务性的，因为您不想隐藏并发请求中的冲突；你想解决它们。确保更新确实完成的唯一方法是检查是否有任何行已更新。

当然，您可能已经猜到这种方法并非万无一失。 “解决”冲突的唯一方法是在放弃之前尝试几次。没有一种自制解决方案能够与硬编码到数据库服务器软件中的解决方案一样好。但它可以非常接近！

存储过程并不能消除冲突，但它确实大大缩短了冲突发生的时间跨度。您不是为待处理的 INSERT 事务“保留”修订号，而是收到最新的修订号并尽快更新静态计数器，为下一次调用 扫清障碍。 GET_REVISION_NUMBER。 （当然，这是序列化的，但仅适用于需要以串行方式执行的过程的极小部分；与许多其他方法不同，算法的其余部分可以自由并行执行。

）使用与上述类似的解决方案，我们发现阻塞冲突的发生率下降了几个数量级。我们能够从本地网络上的六台机器连续提交数千个请求，然后其中一台机器最终卡住。

卡住的机器陷入了循环，从 SQL 服务器请求新的数字，但总是得到空结果。可以这么说，它无法在边缘插入任何单词。这与 SELECT MAX 情况下的冲突行为类似，但非常罕见。您可以用 SELECT MAX 方法（以及任何相关方法）保证的连续编号来换取千倍的可扩展性。这种权衡或多或少是基本的：据我所知，不存在保证连续的、非序列化的解决方案。

结论

当然，所有这些问题都是基于对本地化的半连续数字的需要。如果您可以接受用户不太友好的修订号，则只需公开 DRAWING_REVISION.ID 即可。 （不过，如果你问我的话，暴露代理键本身就是令人讨厌的。）

这里真正的要点是，自定义标识列比它一开始看起来更难实现，并且任何有一天可能需要可扩展性的应用程序都必须非常困难。小心它如何获取新的自定义标识值。

Answer 2

您可以创建一个设置 rev 值的插入触发器。

CREATE TRIGGER RevTrigger ON DRAWING
FOR INSERT
AS
WITH ins AS
    (
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY {another-column}) AS sequence
    FROM inserted
    WHERE REV IS NULL  -- only update rows where REV is not included
    ),
  draw AS
    (
    SELECT ID, MAX(REV) AS REV
    FROM DRAWING
    GROUP BY ID
    )

UPDATE DRAWING
SET REV = COALESCE(draw.REV + ins.sequence, 0)
FROM DRAWING
JOIN ins ON DRAWING.ID = ins.ID AND DRAWING.{another-column} = ins.{another-column}
JOIN draw ON DRAWING.ID = draw.ID

如果同时插入多个具有相同 ID 值的行，您不会指示如何分配 REV 值。换句话说，如果同时添加多个修订版，修订版将如何分配？

此解决方案假设有一个附加列将确定这种情况下的修订顺序（请参阅上面的 {another-column}）。如果您没有这样的列，请在 ROW_NUMBER 函数中将 ORDER BY {another-column} 更改为 ORDER BY 0 。并且，删除以下 AND DRAWING.{another-column} = ins.{another-column}。进行此更改后，插入中具有相同 ID 的所有行将获得相同的 REV。

编辑
上面的脚本仅适用于 SQL Server 2005 及更高版本。这是一种适用于 SQL Server 2000 的解决方案，但不能解决一次插入中多个修订的问题。

CREATE TRIGGER RevTrigger ON DRAWING
FOR INSERT
AS

UPDATE DRAWING
SET REV = COALESCE(draw.REV + 1, 0)
FROM DRAWING
JOIN inserted ON DRAWING.ID = inserted.ID AND DRAWING.{another-column} = inserted.{another-column}
            AND inserted.REV IS NULL
JOIN
    (
    SELECT ID, MAX(REV) AS REV
    FROM DRAWING
    GROUP BY ID
    ) AS draw ON DRAWING.ID = draw.ID