sql - 复合主键自动递增

Question

我有一个名为“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...

有人知道如何解决这个问题吗？

Answer 1

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

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

好的，假设这个表:

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

就我而言，我需要“transactionNo”才能始终为每个契约(Contract)提供正确的下一个值。对我来说，在传统金融系统中重要的是交易中没有任何数字的缺口。

因此，我们需要以下触发器来确保 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 () 来计算出 Inserted 中所有行的正确“下一个”TransactionNo。

它使用而不是插入触发器来工作，并丢弃任何传入的 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

我们正在更新单行和具有混合契约(Contract)号的多行 - 但正确的 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% 确定将始终保持引用完整性。