CHECK约束使用自定义函数

CHECK约束使用自定义函数

最近因为数据重复，所以想增加一个CHECK约束

创建自定义函数的SQL

USE [HYMES]
GO
/****** Object:  UserDefinedFunction [dbo].[PurchaseOrder_no_unique]    Script Date: 2021/5/12 8:21:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[PurchasingList_checkExist](@ProjectID INT,@PartNumber Nvarchar(255),@NodeID Nvarchar(255),@TotalPurchaingID INT)
returns bit
as 
begin
    declare @result bit
	if((select COUNT(1) from purchasingList where projectID=@ProjectID and partNumber=@PartNumber AND nodeID=@NodeID AND totalPurchasingID!=@TotalPurchaingID)>0)
         set @result = 0
    else
        set @result = 1
    return @result
end

将约束添加到表里

经过测试发现后面CHECK条件是只能为这个，其余报错

alter table purchasinglist
add constraint checkExistPart check (dbo.PurchasingList_checkExist(projectid,partnumber,nodeid,totalpurchasingID)=0)
go

遇到冲突

网上查阅说将数据清空，因为涉及到自增ID的问题，所以一直没敢这么干

做个记录以后忘了就来看看吧