title: MSSQL: Update statement avoiding the CHECK constraint
body: <p>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.</p>
  
  <pre><code>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   
  )
  </code></pre>
  
  <p>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. </p>
  
  <pre><code>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) &lt;= 1) OR (IsActive = 0))
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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...
  </code></pre>
  
  <p>The problem occurs if I try to update one of the inactive records to active. For some reason, it allows me. </p>
  
  <pre><code>UPDATE JobOwners SET IsActive = 1
   WHERE LogID = 3
  
  (1 row(s) affected)
  </code></pre>
  
  <p>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. </p>
  
  <p>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.  </p>
  
  <p>Thank you,<br>
  Ben</p>