Simple CHECK constraint is not that easy

2nd Edit . The source code for the function involved looks like this:

ALTER FUNCTION [Fileserver].[fn_CheckSingleFileSource] ( @fileId INT )
RETURNS INT
AS 
    BEGIN
        -- Declare the return variable here
        DECLARE @sourceCount INT ;

        -- Add the T-SQL statements to compute the return value here
        SELECT  @sourceCount = COUNT(*)
        FROM    Fileserver.FileUri
        WHERE   FileId = @fileId
                AND FileUriTypeId = Fileserver.fn_Const_SourceFileUriTypeId() ;

        -- Return the result of the function
        RETURN @sourceCount ;

    END

      

        
        
        
      

    

Edit . The example table is a simplification. I need this to work in Scaler Function / CHECK CONSTRAINT mode. The real deal is not easy.

Original question . Suppose the following table named FileUri

FileUriId, FileId, FileTypeId

I need to write a check constraint so that the FileId is unique to the FileTypeId of 1. You can insert the same FileId as much as you want, but only on one line where the FileTypeId is 1.

Approach that does NOT work:

1) dbo.fn_CheckFileTypeId returns INT with the following logic: SELECT Count(FileId) FROM FileUri WHERE FileTypeId = 1

2) ALTER TABLE FileUri ADD CONSTRAINT CK_FileUri_FileTypeId CHECK dbo.fn_CheckFileTypeId(FileId) <= 1

When I insert FileId 1, FileTypeId 1 twice, a second insert is allowed.

Thank you SO!