简单的CHECK约束不是那么简单

问题描述

第二次编辑:所涉及功能的源代码如下:

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

编辑:示例表只是一种简化.我需要将其用作缩放器功能"/"CHECK CONSTRAINT"操作.现实世界中的安排并非如此简单.

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.

原始问题:假设下表名为FileUri

Original Question: Assume the following table named FileUri

FileUriId，FileId，FileTypeId

FileUriId, FileId, FileTypeId

我需要编写一个检查约束，以使FileId在FileTypeId为1时是唯一的.您可以根据需要插入相同的FileId，但只能插入一行，其中FileTypeId为1.

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.

不可行的方法:

1)dbo.fn_CheckFileTypeId返回INT，其逻辑如下:SELECT Count(FileId) FROM FileUri WHERE FileTypeId = 1

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

当我两次插入FileId 1，FileTypeId 1时，允许第二次插入.

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

谢谢！

推荐答案

您需要创建过滤的唯一索引(SQL Server 2008)

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

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

或使用索引视图(2000和2005)对此进行仿真

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)

这篇关于简单的CHECK约束不是那么简单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！