Conditional Check Constraints on SQL Server

I was on Stack Overflow the other day (something I'm just getting into . . . not sure I care that much for it yet, but we'll see. I'm definitely enjoying it more now that I'm not restricted on my behaviors because I lack reputation points).

In any event, someone asked me a question about conditional check constraints. The consensus seemed to be that you couldn't do it so I figured I'd post the solution on my blog as well.

Basically, the thought was that you couldn't put the check constraint on a subquery or an aggregate so you had to use a trigger only. Well, that's not technically true since you can write a function to do the aggregating for you. I'm not sure I like the idea, but it is in fact possible and I'm looking forward to reading all of your comments about the topic.

So, here's my script that comprises an entire test including a test table, the function, the constraint, some inserts, the results, and cleaning up:

CREATE TABLE CheckConstraint
(
  Id TINYINT,
  Name VARCHAR(50),
  RecordStatus TINYINT
)
GO

CREATE FUNCTION CheckActiveCount(
  @Id INT
) RETURNS INT AS BEGIN
  
  DECLARE @ret INT;
  SELECT @ret = COUNT(*) FROM CheckConstraint WHERE Id = @Id AND RecordStatus = 1;
  RETURN @ret;

END;
GO

ALTER TABLE CheckConstraint
  ADD CONSTRAINT CheckActiveCountConstraint CHECK (dbo.CheckActiveCount(Id) <= 1 OR RecordStatus <> 1);

INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 1);

INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 2);
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);
-- Msg 547, Level 16, State 0, Line 14
-- The INSERT statement conflicted with the CHECK constraint "CheckActiveCountConstraint". The conflict occurred in database "TestSchema", table "dbo.CheckConstraint".
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 2);

SELECT * FROM CheckConstraint;
-- Id   Name         RecordStatus
-- ---- ------------ ------------
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  1
-- 2    Oh no!       1
-- 2    Oh no!       2

ALTER TABLE CheckConstraint
  DROP CONSTRAINT CheckActiveCountConstraint;

DROP FUNCTION CheckActiveCount;
DROP TABLE CheckConstraint;