sql - 复合主键上的自动递增

我有一个名为“ Workspaces”的表，其中“ AreaID”和“ SurfaceID”列用作组合主键。 AreaID引用另一个名为“ Areas”的表，该表仅以AreaID作为主键。我现在要做的是在每个新的AreaID上使surfaceID从1开始缩回。现在，我在表“区域”和“工作区”中使用以下代码：

--Table 'Areas'
CREATE TABLE Areas (
AreaID INT IDENTITY(1,1) PRIMARY KEY,
Areaname VARCHAR(60) UNIQUE NOT NULL
)

--Table 'Workspaces'
CREATE TABLE Workspaces (
AreaID INT
CONSTRAINT ck_a_areaid REFERENCES Areas(AreaID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
SurfaceID INT IDENTITY(1,1)
CONSTRAINT ck_surfaceid CHECK (surfaceid > 0 AND surfaceid < 1001),
Description VARCHAR(300) NOT NULL,
CONSTRAINT ck_workspaces PRIMARY KEY (AreaID, SurfaceID)
)

当我使用上面的代码时，在不同区域中创建新的工作区时会得到如下结果：

AreaID    SurfaceID
1         1
1         2
1         3
2         4
2         5
3         6
Etc...

但是我希望SurfaceID在每个新的areaID上都从1开始计数，所以我想要的结果将是这样的：

AreaID    SurfaceID
1         1
1         2
1         3
2         1
2         2
3         1
Etc...

有谁知道如何解决？

最佳答案

我同意Mr. Linoff's answer的观点，但如果您希望将其存储为物理形式，则可以在insert trigger中进行：

Update Your_Table
set SurfaceID =  ( select max(isnull(SurfaceID,0))+1 as max
                  from Workspaces t
                  where t.AreaID = INSERTED.AreaID )

编辑：*（作为示例，希望了解如何实现它）

在这个问题中，我看到了两个表格，这就是为什么我编写上述代码的原因，但是以下是我的意思的示例：

样表：

CREATE TABLE testTbl
(
    AreaID INT,
    SurfaceID INT, --we want this to be auto increment per specific AreaID
    Dsc VARCHAR(60)NOT NULL
)

触发：

CREATE TRIGGER TRG
ON testTbl
INSTEAD OF INSERT

AS

DECLARE @sid INT
DECLARE @iid INT
DECLARE @dsc VARCHAR(60)

SELECT @iid=AreaID FROM INSERTED
SELECT @dsc=DSC FROM INSERTED

--check if inserted AreaID exists in table -for setting SurfaceID
IF NOT EXISTS (SELECT * FROM testTbl WHERE AreaID=@iid)
SET @sid=1
ELSE
SET @sid=(  SELECT MAX(T.SurfaceID)+1
            FROM testTbl T
            WHERE T.AreaID=@Iid
          )

INSERT INTO testTbl (AreaID,SurfaceID,Dsc)
            VALUES  (@iid,@sid,@dsc)

插：

INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V1');
INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V2');
INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V3');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V4');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V5');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V6');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V7');
INSERT INTO testTbl(AreaID,Dsc) VALUES (3,'V8');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V9');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V10');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V11');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V12');

检查值：

SELECT * FROM testTbl

输出：

AreaID  SurfaceID   Dsc
   1       1        V1
   1       2        V2
   1       3        V3
   2       1        V4
   2       2        V5
   2       3        V6
   2       4        V7
   3       1        V8
   4       1        V9
   4       2        V10
   4       3        V11
   4       4        V12

重要说明：此触发器不会一次处理多行插入，并且像示例一样，需要一次插入一条记录。为了处理多记录插入，需要更改其主体并使用row_number