我的查询:
INSERT into PriceListRows (PriceListChapterId,[No])
SELECT TOP 250 100943 ,N'2'
FROM #AnyTable
该查询工作正常,并且根据需要引发以下异常:
INSERT 语句与 CHECK 约束冲突
“CK_PriceListRows_RowNo_Is_Not_Unqiue_In_PriceList”。冲突
发生在数据库“TadkarWeb”的表“dbo.PriceListRows”中。
但随着变化SELECT TOP 250
to SELECT TOP 251
(是的!只需将 250 更改为 251!)查询成功运行,没有任何检查约束异常!
为什么会有这种奇怪的行为?
NOTES :
我的检查约束是一个检查某种唯一性的函数。它查询 4 个表。
我检查了 SQL Server 2012 SP2 和 SQL Server 2014 SP1
** EDIT 1 **
检查约束函数:
ALTER FUNCTION [dbo].[CheckPriceListRows_UniqueNo] (
@rowNo nvarchar(50),
@rowId int,
@priceListChapterId int,
@projectId int)
RETURNS bit
AS
BEGIN
IF EXISTS (SELECT 1
FROM RowInfsView
WHERE PriceListId = (SELECT PriceListId
FROM ChapterInfoView
WHERE Id = @priceListChapterId)
AND (@rowID IS NULL OR Id <> @rowId)
AND No = @rowNo
AND (@projectId IS NULL OR
(ProjectId IS NULL OR ProjectId = @projectId)))
RETURN 0 -- Error
--It is ok!
RETURN 1
END
** EDIT 2**
检查约束代码(SQL Server 2012 生成的代码):
ALTER TABLE [dbo].[PriceListRows] WITH NOCHECK ADD CONSTRAINT [CK_PriceListRows_RowNo_Is_Not_Unqiue_In_PriceList] CHECK (([dbo].[tfn_CheckPriceListRows_UniqueNo]([No],[Id],[PriceListChapterId],[ProjectId])=(1)))
GO
ALTER TABLE [dbo].[PriceListRows] CHECK CONSTRAINT [CK_PriceListRows_RowNo_Is_Not_Unqiue_In_PriceList]
GO
** EDIT 3 **
执行计划在这里:https://www.dropbox.com/s/as2r92xr14cfq5i/execution%20plans.zip?dl=0 https://www.dropbox.com/s/as2r92xr14cfq5i/execution%20plans.zip?dl=0
** EDIT 4 **
RowInfsView
定义是:
SELECT dbo.PriceListRows.Id, dbo.PriceListRows.No, dbo.PriceListRows.Title, dbo.PriceListRows.UnitCode, dbo.PriceListRows.UnitPrice, dbo.PriceListRows.RowStateCode, dbo.PriceListRows.PriceListChapterId,
dbo.PriceListChapters.Title AS PriceListChapterTitle, dbo.PriceListChapters.No AS PriceListChapterNo, dbo.PriceListChapters.PriceListCategoryId, dbo.PriceListCategories.No AS PriceListCategoryNo,
dbo.PriceListCategories.Title AS PriceListCategoryTitle, dbo.PriceListCategories.PriceListClassId, dbo.PriceListClasses.No AS PriceListClassNo, dbo.PriceListClasses.Title AS PriceListClassTitle,
dbo.PriceListClasses.PriceListId, dbo.PriceLists.Title AS PriceListTitle, dbo.PriceLists.Year, dbo.PriceListRows.ProjectId, dbo.PriceListRows.IsTemplate
FROM dbo.PriceListRows INNER JOIN
dbo.PriceListChapters ON dbo.PriceListRows.PriceListChapterId = dbo.PriceListChapters.Id INNER JOIN
dbo.PriceListCategories ON dbo.PriceListChapters.PriceListCategoryId = dbo.PriceListCategories.Id INNER JOIN
dbo.PriceListClasses ON dbo.PriceListCategories.PriceListClassId = dbo.PriceListClasses.Id INNER JOIN
dbo.PriceLists ON dbo.PriceListClasses.PriceListId = dbo.PriceLists.Id