How are my SQL Server constraints being bypassed?
Check constraints based on UDFs are rubbish. Concurrency, RBAR, isolation etc as you've found out. Some links:

Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates by Alex Kuznetsov

Using a UDF in a check constraint to check validity of history windows (start - end date windows) by Tony Rogerson

Be careful with constraints calling UDFs by Tibor Karaszi


The safest way for SQL Server in this case would be to use standard constraints such as unique and foreign keys. I can't see why you check the folders table for a constraint on the files table though.
To prevent a file and a folder having the same name in a given parent folder only, use an indexed view. Duplicate files or duplicate folders requires table level uniqueness.
CREATE VIEW CheckUnique
WITH SCHEMABINDING
AS
SELECT fo.ParentFoldersID, fo.Name
FROM
   Folders fo
   JOIN
   File fi ON fo.ParentFoldersID  = fi.ParentFoldersID AND fo.Name = fi.Name
GO
CREATE UNIQUE CLUSTERED INDEX IXCU_CheckUnique ON CheckUnique (ParentFoldersID, Name)
GO

Or a trigger.
But never a UDF in a check constraint