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

本文介绍了简单的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)