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

Dyestuff

Don't use a check constraint for this. You can use a unique index on an indexed view (though in 2000 you might have more work to do ensuring that the SET options in use by the connections are compatible with these) – Strang

@MartinSmith: no need for an indexed view. A regular partial index would be enough: create unique index idx_active_jpsid on jobowners (jpsid) where isActive = 1 but I think partial indexes are only available in 2008 and later – Reiterant

@a_horse_with_no_name 2008 and later and the question is tagged 2000 – Strang

@MartinSmith: yes I know it's tagged 2000, but aren't indexed views also not available in 2000? – Reiterant

@a_horse_with_no_name - Indexed views are available in 2000. Just the requirements for set options are a bit fussier (arithabort needs to be on) – Strang

The idea of a filtered (partial) index was intriguing and it works. However, after reading Aaron's (accepted) answer and the link he provides, I had an epiphany which eliminates the need for another index that has maintenance overhead and might not be needed for retrieval queries. If you find the partial index useful for other things, you can ignore this comment. Otherwise, I posted my answer below, along with sample SQL. – Irby

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

Nosewheel

Ha, atleast I wasn't loosing my mind over something basic. Thanks a lot for your help Aaron. I'll give your workaround and the one in the Connect Item a try and see how it comes out. – Dyestuff

This looks like it has a race condition if 2 concurrent connections do an update. – Strang

@Martin yes, you could probably protect it better with transactions. – Nosewheel

Aaron, given your credentials (appreciate that you know what a "twofer" is since I have Canadian relatives, LOL) I would like your opinion on my answer and whether you are OK with my "EDIT" and warning or still feel I should remove my answer. After all, as Martin pointed out (and you agreed) even your answer has the potential for an "issue". I like the filtered index (suggested by a_horse_with_no_name above) but that requires extra IO and storage for index maintenance and can still lead to INSERT/UPDATE failures. Trying not to be a "points hound" if my answer has danger I have not thought of – Irby

Answer 2

EDIT: HUGE caveat. See Aaron's comment on this SO question for reasons you probably want to avoid combining UDFs and CHECK CONSTRAINTS. However, since (even after reading and understanding Aaron's concerns) my answer is still viable in our system because of 1) how our system works and 2) we actually want UPDATE statements to fail in the scenarios he describes, I am leaving my answer here. As it ALWAYS is, it is up to you to make sure you understand the ramifications of using the script in this answer. YOU HAVE BEEN WARNED

I followed the link in Aaron's (accepted) answer. In the description there was a specific piece of text that caught my attention "(to check values that are not passing as parameters)".

That gave me an idea. I have a table with columns CustomerId, ContactId, ContactType all of type "int". The PK is CustomerId and ContactId. I needed to be able to limit each CustomerId to only have one "Primary" Contact (ContactType = 1) but as many "secondary" and "other" contacts as people wanted to add. I had setup my UDF to accept only CustomerId as a parameter. So, I added ContactType as well but since I only cared about ContactType = 1, I just hard-coded the ContactType parameter to 1 inside the function. It worked on SQL2012 but I have no idea about other versions.

Here is a test script. I "squished" together some of the statements to reduce the amount of scrolling needed. Note: the constraint ALLOWS zero Primary Contacts because it would be impossible to set a different Contact as the Primary if you did not first remove an existing Primary.

CREATE TABLE [dbo].[CheckConstraintTest](
    [CustomerId] [int] NOT NULL,
    [ContactId] [int] NOT NULL,
    [ContactType] [int] NULL,
CONSTRAINT [PK_CheckConstraintTest] PRIMARY KEY CLUSTERED (
    [CustomerId] ASC,
    [ContactId] ASC
))
GO

CREATE FUNCTION dbo.OnlyOnePrimaryContact (
    @CustId int, @ContactType int ) RETURNS bit
AS BEGIN
    DECLARE @result bit, @count int
    SET @ContactType = 1 --only care about "1" but needed parm to force SQL to "care" about that column
    SELECT @count = COUNT(*) FROM CheckConstraintTest WHERE [CustomerId] = @CustId AND [ContactType] = @ContactType
    IF @count < 2 SET @result = 1
    ELSE  SET @result = 0
    RETURN @result
END
GO

ALTER TABLE [dbo].[CheckConstraintTest] WITH CHECK ADD CONSTRAINT [SinglePrimaryContact] CHECK  (([dbo].[OnlyOnePrimaryContact]([CustomerId],[ContactType])=(1)))
GO

ALTER TABLE [dbo].[CheckConstraintTest] CHECK CONSTRAINT [SinglePrimaryContact]
GO

INSERT INTO [CheckConstraintTest] (CustomerId, ContactId, ContactType) 
VALUES (1,1,1), (1,2,2), (1,3,2), (1,4,2), (2,1,1)

INSERT INTO [CheckConstraintTest] (CustomerId, ContactId, ContactType) 
VALUES (1,5,1) --This should fail

UPDATE [CheckConstraintTest] --This should fail
SET ContactType = 1
WHERE CustomerId = 1 AND ContactId = 2

UPDATE [CheckConstraintTest] --This should work
SET ContactType = 2
WHERE CustomerId = 1 AND ContactId = 1

INSERT INTO [CheckConstraintTest] (CustomerId, ContactId, ContactType) 
VALUES (1,5,1) --This should work now since we change Cust 1, Contact 1, to "secondary" in previous statement

Irby