T-SQL crazy function result

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.