T-SQL crazy function result

Question

I have this function:

CREATE FUNCTION CheckAkvNames (@Name VARCHAR(20))
RETURNS INT
AS
BEGIN
     DECLARE @NoTexist int = 1

     SELECT 
         @NoTexist = CASE WHEN COUNT(*) > 0 THEN 0 ELSE 1 END 
     FROM 
         [dbo].[Names] 
     WHERE 
         [Name] = @Name  

     RETURN @NoTexist
END 
GO

ALTER TABLE [dbo].[Names] 
    ADD CONSTRAINT chkNames CHECK(dbo.CheckAkvNames([Name]) = 1);
GO

The problem is, when I run this on empty table I can't insert ...

So this change works:

CASE WHEN (COUNT(*) - 1) > 0 THEN 0 ELSE 1 END 

WHY? Any ideas?

Edit: Aim is to insert only names that are not in the table. I know it would be better to use key, point of the question is not to find better solution but why this solution does not work.

Answer 1

The constraint you added to the table actually means that you can't insert any name in the table, because for any value inserted in the table the function should return 1.
This is impossible because if the name was inserted then the constraint would be violated.
This is why count(*) - 1 works: if there is already a name inserted and you tried to insert the same name then the constraint would be violated.

Answer 2

If you want unique names in a table, do not use a check constraint, use a unique constraint (or equivalently a unique index):

ALTER TABLE [dbo].[Names] 
    ADD CONSTRAINT unq_names_name UNIQUE (Name);