SQL唯一索引
我有两个表(简体) - 卡车和拖车SQL唯一索引
卡车
TruckID int identity
Trailer1 int null
Trailer2 int null
拖车
TrailerID int identity
由于拖车外键可为空,一辆卡车可以根本没有拖车,有一辆拖车或两辆拖车的LER。
如何在卡车表上建立索引以确保相同的TrailerID永远不能使用两次?
对于一个预告片,我可以创建一个计算字段
IsNull(TrailerID, -1 * TruckID)
,并创建这个唯一索引。但我如何管理这两个预告片?
我不认为你可以单独做索引。你需要有你的身份,因为你有。我想你将不得不impliment您的更新触发如果发生冲突,这将确保trailerID不会在任何
Select Trailer1 FROM Trucks
UNION
Select Trailer2 FROM Trucks
http://msdn.microsoft.com/en-gb/library/ms189799%28v=sql.105%29.aspx存在触发
你应该还是有索引回滚Trailer1和Trailer2上,这样优化器可以查找TrailerID是否存在于任一列中。
您可以分别对Traileid1和TrailerId2设置UNIQUE约束。
CREATE TABLE Trailers
(
TrailerID int identity PRIMARY KEY NOT NULL,
t_type varchar(10) null
)
CREATE TABLE Trucks
(
TruckID int identity PRIMARY KEY NOT NULL,
Trailer1 int UNIQUE null,
Trailer2 int UNIQUE null
FOREIGN KEY (Trailer1) REFERENCES Trailers(TrailerID),
FOREIGN KEY (Trailer2) REFERENCES Trailers(TrailerID),
)
我认为OP正试图阻止一个出现在Trailer1中的值出现在Trailer2中(反之亦然) – 2013-04-25 09:09:53
您将需要创建一个索引视图,这将有效地结合预告片。
对于这一点,你必须创建一个微小的支撑表:
CREATE TABLE place (id INT NOT NULL PRIMARY KEY)
INSERT
INTO place
VALUES (1),
(2)
GO
CREATE VIEW
v_truck_trailers
WITH SCHEMABINDING
AS
SELECT t.id AS truckId,
p.id AS placeId,
CASE p.id WHEN 1 THEN trailer1 WHEN 2 THEN trailer2 END AS trailerId
FROM dbo.truck t
JOIN dbo.place p
ON CASE p.id WHEN 1 THEN trailer1 WHEN 2 THEN trailer2 END IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX
ux_v_truck_trailers_truck_place
ON v_truck_trailers (truckId, placeId)
GO
CREATE UNIQUE INDEX
ux_v_truck_trailers_trailer
ON v_truck_trailers (trailerId)
GO
现在让我们来试试吧:
INSERT
INTO truck
VALUES (1, 1, NULL) -- succeeds
INSERT
INTO truck
VALUES (2, 2, 3) -- also succeeds
INSERT
INTO truck
VALUES (3, NULL, 2) -- fails as trailer 2 is already used on truck 2, even if on another place.
如果你可以改变表结构,我建议第三个表TrailersTrucks
TrailersTrucks
TrailersTrucksID int identity
TruckID int not null
TrailerID int not null
[TrailerNo int] - optional
变化卡车声明
Trucks
TruckID int identity
TrailersTrucksID int null
然后你就可以在TrailerID使用唯一索引。您可能还想引入TrailerNo并将其限制为1和2的值(或使用枚举),并在TruckID和TrailerNo上添加唯一索引...这样就不可能在卡车中添加3个或更多预告片...您如果你需要它总是可以扩展约束(f.ex. for train)。
这是建议的解决问题的方法,那么你会得到标准化的数据库。
但是我知道这并不总是可能的,原因很多:)
是的。这将是一个新项目的明显出路。不幸的是,我们目前不可能创建一个多对多的关系。这会使应用程序其他部分的许多SQL变得复杂。不管怎么说,还是要谢谢你! – navigator 2013-04-26 03:56:26
同意。在尝试更新或插入之前,编写SP来首先运行Select语句来检查预告片的存在可能更容易。但我正在寻找一个使用索引的优雅解决方案。 – navigator 2013-04-26 04:12:40
嗯,我不喜欢触发器,因为它们可能很难调试。你可以在联合上创建一个视图,然后(你应该可以)使得结果列唯一。 (我怀疑会触发引擎盖下)。http://msdn.microsoft.com/en-us/library/aa933148%28v=sql.80%29.aspx表明这应该工作 – 2013-04-26 08:55:54
@IanP:你不能在'UNION'上创建一个索引视图。 – Quassnoi 2013-04-26 13:37:19