在MS2000中工作，我有一个名为JobOwners的表，该表将Jobs（JPSID）映射到拥有它们的雇员（EmpID）。它还包含他们开始拥有该工作的日期（DateStarted），他们停止拥有该工作的日期（DateEnded）以及所有权是否处于活动状态（IsActive）。看起来像这样。

CREATE TABLE JobOwners
(
    LogID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    JPSID int NOT NULL FOREIGN KEY REFERENCES JobsPerShift(JPSID),
    EmpID int NOT NULL FOREIGN KEY REFERENCES Employees(EmpID),
    DateStarted datetime,
    DateEnded datetime,
    IsActive tinyint NOT NULL   
)

尽管没有活动的重复应该没问题，但不应有活动的JPSID重复。通过一些研究，我发现可以使用CHECK约束上的函数来完成此操作。

CREATE FUNCTION CheckActiveCount(@JPSID INT) 
RETURNS INT AS 
BEGIN
    DECLARE @result INT
    SELECT @result = COUNT(*) FROM JobOwners WHERE JPSID = @JPSID AND IsActive = 1
    RETURN @result
END
GO

ALTER TABLE JobOwners 
 ADD CONSTRAINT CK_JobOwners_IsActive
 CHECK ((IsActive = 1 AND dbo.CheckActiveCount(JPSID) <= 1) OR (IsActive = 0))

这足够好用。由于没有其他活动的JPSID 2，它将允许我插入具有IsActive 1的JPSID2。它将使我能够插入具有IsActive 0的JPSID
2，因为当IsActive为0时不应用该检查。但是，再次将JPSID 2与IsActive 1插入，因为它与约束冲突。见下文。

INSERT INTO JobOwners
 VALUES(2,2,NULL,NULL,1)

(1 row(s) affected)

INSERT INTO JobOwners
 VALUES(2,2,NULL,NULL,0)

(1 row(s) affected)

INSERT INTO JobOwners
 VALUES(2,3,NULL,NULL,1)

INSERT statement conflicted with COLUMN FOREIGN KEY constraint...

如果我尝试将不活动的记录之一更新为活动，则会出现问题。由于某种原因，它允许我。

UPDATE JobOwners SET IsActive = 1
 WHERE LogID = 3

(1 row(s) affected)

如果我再次运行同一条语句，则它与约束冲突，但不是第一次冲突。这个应用程序的前端永远不会将非活动记录更改为活动记录，它只会插入一个新记录，但这仍然不是我希望该表允许的内容。

我想知道是否最好将活跃的工作负责人分开，并为工作负责人的历史提供一个单独的表，但是我不确定这里的最佳做法。任何帮助将不胜感激。

谢谢你，
本