T-SQL唯一标识列作为组合主键的一部分

Suppose that you have a table with the following structure:

假设您有一个具有以下结构的表:

CREATE TABLE [log] (
  [type] int NOT NULL,
  [stat] nvarchar(20) NOT NULL,
  [id] int IDENTITY (1, 1) NOT NULL,
  descr nvarchar(20),
  PRIMARY KEY ([type], [stat], [id])
)

Is it possible to force the [id] to be incremented only whenever the other two PK fields have the same values, and not independently as is now? For instance:

当其他两个PK字段具有相同的值且不像现在这样独立时，是否有可能强制[id]只递增?例如:

type    stat      id     descr
5       ERROR     1      Test  <---
3       WARNING   1      Test
5       ERROR     2      Test  <---
2       ERROR     1      Test
1       WARNING   1      Test
5       WARNING   1      Test
5       ERROR     3      Test  <---

3 个解决方案

#1

No. The purpose of an IDENTITY (or SEQUENCE) is only to generate an incremental integer. There may be gaps as values are not reused, and values may be reserved but not used.

不。标识(或序列)的目的只是生成一个递增的整数。当值不被重用，值可能被保留但不被使用时，可能会出现差异。

You can use an expression in queries to show the desired value.

可以在查询中使用表达式来显示所需的值。

ROW_NUMBER() OVER (PARTITION BY type, stat ORDER BY id) AS Seq

#2

This i think would get your job done

我想这能完成你的工作

CREATE TABLE [LOG1] (
  [TYPE] INT NOT NULL,
  [STAT] NVARCHAR(20) NOT NULL,
  [ID] INT ,
  DESCR NVARCHAR(20),
  PRIMARY KEY ([TYPE], [STAT], [ID])
)

CREATE TRIGGER TR_LOG
ON [DBO].[LOG1]
INSTEAD OF INSERT
AS
 BEGIN
DECLARE @CNT INT=0
IF EXISTS(SELECT 'X' FROM LOG1 A JOIN INSERTED B ON A.TYPE=B.TYPE AND A.STAT=B.STAT)
SET @CNT=(SELECT COUNT(*) FROM LOG1 A JOIN INSERTED B ON A.TYPE=B.TYPE AND A.STAT=B.STAT)
PRINT @CNT
INSERT INTO LOG1(TYPE,STAT,ID,DESCR) 
SELECT TYPE,STAT,ID+@CNT,DESCR FROM INSERTED
END

#3

That is not 3NF
I would fix it with data design

这不是3NF，我用数据设计来修正

CREATE TABLE [logBase] (
  [id] int IDENTITY (1, 1) NOT NULL,
  PRIMARY KEY ([id])
)

CREATE TABLE [status] (
  [id] int IDENTITY (1, 1) NOT NULL,
  descr nvarchar(20),
  PRIMARY KEY ([id])
)

CREATE TABLE [log] (
  [type] int NOT NULL,
  [statusID] nvarchar(20) NOT NULL,
  [baseID] int NOT null, 
  descr nvarchar(20),
  PRIMARY KEY ([type], [statusID], [baseID])
)

#1