MSSQL: Update statement avoiding the CHECK constraint

Question

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   
)

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

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

Answer 1

There is a known issue where certain operations will lead to a check constraint that calls a UDF to be bypassed. The bug was listed on Connect (before it was scuttled and all the links were orphaned) and it has been acknowledged, but closed as Won't Fix. This means we need to rely on workarounds.

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:

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