MSSQL：更新语句避免了CHECK约束

Question

在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)

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

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

谢谢你，
本

Answer 1

存在一个已知问题，其中某些操作将导致检查约束，从而导致绕过UDF。该错误已在Connect上列出（在被破坏之前，所有链接都被孤立），并且已经得到确认，但由于无法修复而被关闭。这意味着我们需要依靠变通办法。

我的第一个解决方法可能是代替更新触发器。感谢Martin使我保持诚实并进一步进行了测试-
我发现我无法防止在同一条语句中将两行更新为1。我已经纠正了逻辑并添加了一个事务来帮助防止出现竞争情况：

CREATE TRIGGER dbo.CheckJobOwners ON dbo.JobOwners
INSTEAD OF UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRANSACTION;

  UPDATE j SET IsActive = 1 -- /* , other columns */
    FROM dbo.JobOwners AS j INNER JOIN inserted AS i
    ON i.LogID = j.LogID
    WHERE i.IsActive = 1 AND NOT EXISTS 
    (    -- since only one can be active, we don't need an expensive count:
      SELECT 1 FROM dbo.JobOwners AS j2
        WHERE j2.JPSID = i.JPSID
        AND j2.IsActive = 1 AND j2.LogID <> i.LogID
    )
    AND NOT EXISTS 
    (    -- also need to protect against two rows updated by same statement: 
      SELECT 1 FROM inserted AS i2
        WHERE i2.JPSID = i.JPSID
        AND i2.IsActive = 1 AND i2.LogID <> i.LogID
    );

  -- *if* you want to report errors:
  IF (@@ROWCOUNT <> (SELECT COUNT(*) FROM inserted WHERE IsActive = 1))
    RAISERROR('At least one row was not updated.', 11, 1);

  -- assume setting active = 0 always ok & that IsActive is not nullable
  UPDATE j SET IsActive = 0 -- /* , other columns */
    FROM dbo.JobOwners AS j INNER JOIN inserted AS i
    ON j.LogID = i.LogID
    WHERE i.IsActive = 0;

  COMMIT TRANSACTION;
END
GO

（我之所以选择代替触发器而不是代替触发器的唯一原因是，您只更新需要更新的行，而不必在事实发生后回滚（这不会让您仅在无效情况下回滚无效的更新）。
-行更新））。

关于此问题，这里有很多很好的讨论：

https://web.archive.org/web/20171013131650/http://sqlblog.com/blogs/tibor_karaszi/archive/2009/12/17/be-
careful-with-constraints-calling-
udfs.aspx