Need to add a Table Level Constraint to prevent bad data - Constraint is not working
You might want to look up in some reliable resource (e.g. the f... manual, or in the SQL standard), when exactly CHECK constraints are verified.  And if it turns out to be "after the INSERT has been processed", then you might try to figure out how the count of existing matching rows can ever be zero.

Apart from that, most engines disallow referencing tables in CHECK constraints.  It's hopelessly annoying, but there is a very good reason for it !!!.  That reason is that the CHECK constraint should also be checked in the event of an update occurring to the referenced table, that somehow impacts the result of this CHECK constraint for some row of the table in which it is declared.

Engines typically do not know how to do this efficiently.  So they impose that limitation upon the users so that at least the integrity of the data is not compromised.  Do not make the mistake of thinking that you are smart when you discover that a UDF with some SELECT can circumvent the limitation.  You're only deluding yourself.  And a couple of months later, you'll just be the next one to ask yet another "CHECK constraint not working" question.