时间戳

首页 > 解决方案 > 自动递增复合主键

sql - 自动递增复合主键

问题描述

我正在研究一个包含事件表和违规表的数据库。事件表的主键是 IncidentID。那么违规表的主键就是 IncidentID,ViolationNum。

CREATE TABLE Incident (
IncidentID FLOAT,
Plate VARCHAR(10) NOT NULL,
PlateState INTEGER NOT NULL,
IncidentTypeID INTEGER NOT NULL,
CommonPlaceID INTEGER NOT NULL,
Officer INTEGER,
DateStart DATE NOT NULL,
DateEnd DATE,
Comments TEXT,
PRIMARY KEY (IncidentID),
FOREIGN KEY (Plate, PlateState) REFERENCES Vehicle (Plate, PlateState),
FOREIGN KEY (IncidentTypeID) REFERENCES IncidentTypeLookUp (IncidentTypeID),
FOREIGN KEY (CommonPlaceID) REFERENCES CommonPlace (CommonPlaceID),
FOREIGN KEY (Officer) REFERENCES Officer (BadgeNumber));

CREATE TABLE Violation (
IncidentID float,
ViolationNum INTEGER,
ViolationTypeID INTEGER NOT NULL,
DateOfViolation DATE NOT NULL,
Comments TEXT,
PRIMARY KEY (IncidentID,ViolationNum),
FOREIGN KEY (IncidentID) REFERENCES Incident (IncidentID),
FOREIGN KEY (ViolationTypeID) REFERENCES ViolationTypeLookUp (ViolationTypeID));

目标是让每个 IncidentID ex 的违规编号增加 1。2020001,1 2020001,2 2020001,3 2020002,1 2020002,2

我编写了以下触发器，每个触发器都有自己的错误。

create trigger violationCounter 
on violation
instead of insert
as 
begin   
declare @iid float
declare @vnum int
declare @vtid int
declare @date date
declare @comm text

select @iid=IncidentID from inserted
select @vtid=ViolationTypeID from inserted
select @date = DateOfViolation from inserted
select @comm = comments from inserted

IF NOT EXISTS (Select * from Violation where IncidentID=@iid)
set @vnum = 1
else 
set @vnum = (select max(ViolationNum)+1
                from Violation
                where IncidentID = @iid)

insert into Violation 
values (@iid,@vnum,@vtid,@date,@comm) 
end 
go

此触发器不接受 Violation 作为 "@vnum=" select 语句中的对象。

或者

create trigger violationcounter
on Violation

instead of insert

as
begin

begin transaction

declare @iid float
declare @vnum int

IF EXISTS  (Select * from Violation where @iid=IncidentID)
set @vnum = (select max(ViolationNum)+1
                from Violation
                where IncidentID = @iid);
else 
set @vnum = 1;

INSERT INTO Violation (IncidentID,ViolationNum, ViolationTypeID,DateOfViolation,Comments)
SELECT IncidentID, @vnum, ViolationTypeID, DateOfViolation,Comments
FROM inserted

Commit transaction

end

此触发器被接受，但在输入数据时它不会增加违规编号。

我在这里到底做错了什么？

标签： sqlsql-server

解决方案

正确的实现是使用序列或 IDENTITY 生成 ViolationNum，即使您将其保留为复合主键中的第二列。例如：

CREATE TABLE Violation (
IncidentID INTEGER,
ViolationNum INTEGER IDENTITY NOT NULL,
ViolationTypeID INTEGER NOT NULL,
DateOfViolation DATE NOT NULL,
Comments TEXT,
PRIMARY KEY (IncidentID,ViolationNum),
FOREIGN KEY (IncidentID) REFERENCES Incident (IncidentID),
FOREIGN KEY (ViolationTypeID) REFERENCES ViolationTypeLookUp (ViolationTypeID));

并且忘记为每个 IncidentID 重新开始的 ViolationNum 值。

推荐阅读