复合主键上的自动递增 [英] auto increment on composite primary key

问题描述

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

I have a table called 'Workspaces' where the columns 'AreaID' and 'SurfaceID' work as a composite primary key. The AreaID references to another table called 'Areas' which only has AreaID as the primary key. What I want to do now is to make the surfaceID recound from 1 on every new AreaID. Right now I'm using the following code for the tables 'Areas' and 'Workspaces':

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

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

When I use the code above I get a result like this when creating new workspaces in different areas:

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

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

But I want the SurfaceID to recount from 1 on every new areaID, so my desired result would be like this:

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

有人知道如何解决此问题吗?

Does anyone know how this can be fixed?

推荐答案

我同意先生. Linoff的答案，但是如果要存储它，可以在insert trigger:

I am agree with Mr. Linoff's answer but if you want to store it phisicaly, you can do it within an insert trigger:

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

---

* (例如，如何实现该示例的示例)

---

*(as an example wanted for how to implement it)

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

In the question I have seen two table that's why I have wrote the code as above, but following is a sample for what I meant:

样本表:

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

IMPORTANT NOTICE: this trigger does not handle multi row insertion once and it is needed to insert single record once like the example. for handling multi record insertion it needs to change the body of and use row_number

这篇关于复合主键上的自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！