MSSQL：更新语句避免CHECK约束 [英] MSSQL: Update statement avoiding the CHECK constraint
问题描述
在MS2000中，我有一个名为JobOwners的表，将Jobs（JPSID）映射到拥有它们的Employees（EmpID）。它还包含他们开始拥有该作业的日期（DateStarted），他们停止拥有该作业的日期（DateEnded）和所有权处于活动状态（IsActive）。看起来像这样。
Working in MS2000, I have a table called JobOwners that maps Jobs (JPSID) to the Employees that own them (EmpID). It also contains the date they started owning that job (DateStarted), date they stopped owning that job (DateEnded) and if the ownership is active (IsActive). Looks like this.
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约束上的函数来完成这项工作。
There should be no duplicates of JPSID that are active, although inactive duplicates should be fine. With some research I found I could accomplish this using a function on a CHECK constraint.
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))
这个工作得很好。它将允许我使用IsActive 1插入JPSID 2，因为没有其他活动的JPSID 2.它将允许我插入JPSID 2与IsActive 0，因为当IsActive为0时不应用检查。它拒绝当我尝试再次插入JPSID 2与IsActive 1，因为它与约束冲突。见下文。
This works well enough. It will allow me to insert JPSID 2 with IsActive 1, as there is no other active JPSID 2. It will let me insert JPSID 2 with IsActive 0, because the check isn't applied when IsActive is 0. It rejects when I try to insert JPSID 2 with IsActive 1 again though, because it conflicts with the constraint. See below.
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...
如果我尝试将其中一个非活动记录更新为活动，则会出现问题。由于某种原因，它允许我。
The problem occurs if I try to update one of the inactive records to active. For some reason, it allows me.
UPDATE JobOwners SET IsActive = 1
WHERE LogID = 3
(1 row(s) affected)
如果我再次运行相同的语句，那么它与约束冲突，第一次。这个应用程序的前端永远不会将非活动记录更改为活动记录，它只是插入一个新记录，但它仍然不是我想要的表允许。
If I run the same statement again, then it conflicts with the constraint, but not the first time. The front end of this app would never change an inactive record to active, it would just insert a new record, but it's still not something I'd like the table to allow.
我想知道是否最好将活动的作业所有者分开，并为作业所有者历史记录创建一个单独的表，但我不确定最佳实践这里。
任何帮助将非常感谢。
I'm wondering if it might be best to separate the active job owners and have a seperate table for job owner history, but I'm not certain on the best practice here. Any help would be greatly appreciated.
谢谢
本
Thank you,
Ben
推荐答案
有一个已知的问题，某些操作将导致调用UDF被绕过的检查约束。 在连接上列出的错误已被确认，但已关闭为未修复。这意味着我们需要依靠解决方法。
There is a known issue where certain operations will lead to a check constraint that calls a UDF to be bypassed. The bug is listed on Connect and it has been acknowledged, but closed as Won't Fix. This means we need to rely on workarounds.
我的第一个解决方法可能是一个而不是更新触发器。感谢Martin保持我的诚实，并让我进一步测试 - 我发现我没有保护在同一语句中更新为1的两行。我已更正逻辑并添加了一个事务以帮助防止竞争条件：
My first workaround would probably be an instead of update trigger. Thanks to Martin for keeping me honest and for making me test this further - I found that I did not protect against two rows being updated to 1 in the same statement. I've corrected the logic and added a transaction to help prevent a race condition:
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
（我只有一个原因而不是触发器是你只更新你需要更新的行，而不是必须回滚事实（这将不会让你只回滚无效的更新的情况下多行更新））。
(My only reason for an instead of instead of after trigger is that you only update the rows you need to update, instead of having to rollback after the fact (which won't let you only rollback the invalid updates in the case of a multi-row update)).
这里有很多关于这个问题的很好的讨论：
There is a lot of good discussion about this issue here:
您还可以考虑在此Connect项的注释中提到的解决方法this）：
And you may also consider the workaround mentioned in the comments of this Connect item (I haven't tried this):
这篇关于MSSQL：更新语句避免CHECK约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
