复合主键的自增

Question

复合主键的自增

23

我有一个名为“工作区”的表，在该表中，“AreaID”和“SurfaceID”列作为复合主键。 AreaID引用了另一张只有AreaID作为主键的表格称为“区域”。现在我想做的是使每个新的AreaID从1开始递增SurfaceID。目前我正在为“区域”和“工作区”表使用以下代码：

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

有人知道如何修复这个问题吗？

- xerzina

6

自增无法像这样使用。 - juergen d

你可以使用另一张表来跟踪每个区域的最后一个表面ID，然后使用触发器来管理它。 - Bob

好的，我还是比较新手，请问您能提供一些代码示例吗？ - xerzina

1

为什么你想这样做？整数ID应该是没有意义的，那么如果你只使用自动编号，有什么区别呢？为什么你需要重新开始编号，这是非常危险的，如果你不完全正确地执行它，会导致竞争条件和数据完整性错误。这是一个要求，我会100%反对给我的人。你每天都在浪费处理时间去做一些不必要的事情。唯一接受它作为要求的时候是如果它是法律要求。 - HLGEM

3个回答

4

你不能轻易地做你想要的事情。你可以使用触发器来实现，但这是一个相当丑陋的解决方案。您可以通过使用单个标识主键并在输出时计算您想要的数字来接近您想要的结果：

CREATE TABLE Workspaces (
    WorkspacesId int not null identity(1, 1) primary key,
    AreaID INT,
    Description VARCHAR(300) NOT NULL,
    CONSTRAINT ck_a_areaid REFERENCES Areas(AreaID) ON DELETE CASCADE ON UPDATE NO ACTION,
);

然后当您查询（或在视图中）：

select w.*, row_number() over (partition by areaId
                               order by WorkspaceId) as SurfaceId
from Workspaces

注意：这并不检查surfaceId的最大值。如果你真的需要实现这个逻辑，那么你将需要使用触发器。

- Gordon Linoff

4

我同意林夫先生的回答，但如果你想将其物理存储，可以在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。

- void

出于性能考虑，将该信息存储在单独的表中而不是使用max函数更好吗？ - Bob

由于我不知道您的模式结构和数据的预估容量，所以我无法给您一个确切的答案，但最好向林先生询问，他是数据挖掘和数据库知识的专家，比我更懂行。如果他有时间并且能够回答您的问题，那就更好了！ - void

2

您无法更新已插入的虚拟表，您需要更新基本表。最可能需要使用instead of触发器来实现此目的。当然，这种解决方案开始涉及并发问题。 - Sean Lange

@Farhęg：我是新手，你能否给一个完整的触发器代码示例？ - xerzina

成功了！忘记为所有列选择值了！非常感谢你！:-D - xerzina

显示剩余7条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- soddoff Baldrick · Accepted Answer

以下是适用于多行的解决方案。

感谢jFun为单行插入所做的工作，但这种触发器并不是真正安全的使用方式。

好的，假设有这个表：

create table TestingTransactions (
   id int identity,
   transactionNo int null,
   contract_id int not null,
   Data1 varchar(10) null, 
   Data2 varchar(10) null
);

在我的情况下，我需要“transactionNo”始终具有每个合同的正确下一个值。在遗留金融系统中对我很重要的是，transactionNo编号没有间隙。

因此，我们需要以下触发器来确保transactionNo列的引用完整性。

CREATE TRIGGER dbo.Trigger_TransactionNo_Integrity 
   ON  dbo.TestingTransactions 
   INSTEAD OF INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Discard any incoming transactionNo's and ensure the correct one is used.
    WITH trans 
         AS (SELECT F.*, 
                    Row_number() 
                      OVER ( 
                        ORDER BY contract_id) AS RowNum, 
                    A.* 
             FROM   inserted F 
                    CROSS apply (SELECT Isnull(Max(transactionno), 0) AS 
                                        LastTransaction 
                                 FROM   dbo.testingtransactions 
                                 WHERE  contract_id = F.contract_id) A), 
         newtrans 
         AS (SELECT T.*, 
                    NT.minrowforcontract, 
                    ( 1 + lasttransaction + ( rownum - NT.minrowforcontract ) ) AS 
                       NewTransactionNo 
             FROM   trans t 
                    CROSS apply (SELECT Min(rownum) AS MinRowForContract 
                                 FROM   trans 
                                 WHERE  T.contract_id = contract_id) NT) 
    INSERT INTO dbo.testingtransactions 
    SELECT Isnull(newtransactionno, 1) AS TransactionNo, 
           contract_id, 
           data1, 
           data2 
    FROM   newtrans 
END
GO

好的，我承认这是一个相当复杂的触发器，几乎使用了所有技巧，但这个版本应该可以在 SQL 2005 上运行。脚本利用了 2 个 CTE、2 个交叉应用和 Row_Num() 函数来计算所有插入的行的正确“下一个”TransactionNo。

它使用了 instead of insert 触发器，在丢弃任何传入的 transactionNo 的情况下，用“NEXT” transactionNo 替换它们。

因此，我们现在可以运行这些更新：

delete from dbo.TestingTransactions
insert into dbo.TestingTransactions (transactionNo, Contract_id, Data1)
values (7,213123,'Blah')

insert into dbo.TestingTransactions (transactionNo, Contract_id, Data2)
values (7,333333,'Blah Blah')

insert into dbo.TestingTransactions (transactionNo, Contract_id, Data1)
values (333,333333,'Blah Blah')

insert into dbo.TestingTransactions (transactionNo, Contract_id, Data2)
select 333  ,333333,'Blah Blah' UNION All
select 99999,44443,'Blah Blah' UNION All
select 22,   44443 ,'1' UNION All
select 29,   44443 ,'2' UNION All
select 1,    44443 ,'3'

select * from dbo.TestingTransactions
order by Contract_id,TransactionNo

我们正在更新单行和混合合同编号的多行，但正确的交易号会覆盖传入的值，从而获得预期的结果。

id  transactionNo  contract_id  Data1       Data2
117             1        44443  NULL        Blah Blah
118             2        44443  NULL        1
119             3        44443  NULL        2
120             4        44443  NULL        3
114             1       213123  Blah        NULL
115             1       333333  NULL        Blah Blah
116             2       333333  Blah Blah   NULL
121             3       333333  NULL        Blah Blah

我对并发的看法很感兴趣。我非常确定这两个CTE将被视为单个传递，因此，我99.99%确定引用完整性将始终得到保持。