2

2nd Edit: The source code for the involved function is as follows:

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 as a Scaler Function / CHECK CONSTRAINT operation. The real-world arrangement is not so simple.

Original Question: Assume the following table named FileUri

FileUriId, FileId, FileTypeId

I need to write a check constraint such that FileId are unique for a FileTypeId of 1. You could insert the same FileId as much as you want, but only a single row where FileTypeId is 1.

The approach that DIDN'T work:

1) dbo.fn_CheckFileTypeId returns INT with 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, the second insert is allowed.

Thanks SO!

bopapa_1979
  • 8,949
  • 10
  • 51
  • 76

2 Answers2

4

You need to create a filtered unique index (SQL Server 2008) 

CREATE UNIQUE NONCLUSTERED  INDEX ix ON YourTable(FileId) WHERE FileTypeId=1

or simulate this with an indexed view (2000 and 2005)

CREATE VIEW dbo.UniqueConstraintView
WITH SCHEMABINDING
AS
SELECT FileId
FROM dbo.YourTable
WHERE FileTypeId = 1

GO 
CREATE UNIQUE CLUSTERED  INDEX ix ON dbo.UniqueConstraintView(FileId)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • This for the remark. The sample was a simplification, which I should have specified. My apologies. The real-world instance does need to work as a check constraint. – bopapa_1979 Aug 27 '11 at 04:40
  • @Eric - Well [unless you are using snapshot isolation](http://sqlblog.com/blogs/hugo_kornelis/archive/2006/09/15/Snapshot-and-integrity-part-4.aspx) the general approach you suggest in the question looks like it should work though not as efficiently as an index based constraint. Are you using snapshot? If not are you sure there is not an error in your function code? Have you tested in isolation? – Martin Smith Aug 27 '11 at 11:29
  • I was (predictably) able to modify my schema slightly and use the simple unique constraint approach you suggested. Nod. – bopapa_1979 Aug 28 '11 at 05:29
  • I added the original check function up top just in case you care to see if you can spot a problem. Clearing the Fileserver.FileUri table and inserting a single row with FileUriTypeId = 1, then selecting that function with the new FileId returned 1. Inserting a 2nd row with same FileId, FileUriTypeId = 1, then selecting the function again returned 2. Don't understand why the check was allowing the 2nd insert. – bopapa_1979 Aug 28 '11 at 05:31
  • @Eric - Looks like it should work to me and have tested it and it does work this end. Are you sure you were calling the right function in your check constraint? `dbo.fn_CheckFileTypeId(FileId)` vs `[Fileserver].[fn_CheckSingleFileSource] ` – Martin Smith Aug 28 '11 at 11:13
1

Why don't you make FieldTypeID and Field both the primary key of the table?

Or at least a Unique Index on the table. That should solve your problem.

Icarus
  • 63,293
  • 14
  • 100
  • 115