需要添加表級約束以防止不良數據 - 約束不起作用

我有一個表，它的標誌可以設置為 0 或 1。我需要確保該表中的每個索賠記錄的標誌只設置為 1 一次。因此，換句話說，我可能對一項索賠有多個記錄，除了一個以外的所有記錄都應設置為 0。下表是

CREATE TABLE [dbo].[ClaimAccounting_Assignments](
[PracticeID] [int] NULL,
[ClaimID] [int] NULL,
[ClaimTransactionID] [int] NULL,
[InsurancePolicyID] [int] NULL,
[InsuranceCompanyPlanID] [int] NULL,
[PatientID] [int] NULL,
[LastAssignment] [bit] NULL,
[Status] [bit] NULL,
[PostingDate] [datetime] NOT NULL,
[EndPostingDate] [datetime] NULL,
[LastAssignmentOfEndPostingDate] [bit] NULL,
[EndClaimTransactionID] [int] NULL,
[DKPostingDateID] [int] NULL,
[DKEndPostingDateID] [int] NULL,
[RelativePrecedence] [int] NULL
) ON [PRIMARY]

這是我為實現這一點而創建的函式的約束

Alter FUNCTION FN_IsLastAssigned(@ClaimId INT)
RETURNS INT
AS 

BEGIN
DECLARE @LastAssignment Int


SET @LastAssignment=(
SELECT COUNT(*)
FROM ClaimAccounting_Assignments AS caa
WHERE claimId=@ClaimID AND caa.LastAssignment=1)

RETURN @LastAssignment

END 

這是約束 - 它應該檢查該聲明在插入表時沒有具有 LastAssignment=1 的目前記錄：

ALTER TABLE ClaimAccounting_Assignments
WITH NOCHECK
ADD CONSTRAINT  CK_Constraint_CAA_LastAssignments
CHECK ( dbo.FN_IsLastAssigned(ClaimId)=0)

我有一種感覺，我錯過了一些愚蠢的東西，但我不知道。任何幫助將不勝感激

如果需要，這裡有一些數據可以用來進行測試。

INSERT INTO ClaimAccounting_Assignments

VALUES  (1
   , -- PracticeID
    1191
   , -- ClaimID
    12345
   , -- ClaimTransactionID
    0
   , -- InsurancePolicyID
    0
   , -- InsuranceCompanyPlanID
    0
   , -- PatientID
   0
   , -- LastAssignment
    NULL
   , -- Status
    '2012-07-03 16:56:49'
   , -- PostingDate
    '2012-07-03 16:56:49'
   , -- EndPostingDate
    NULL
   , -- LastAssignmentOfEndPostingDate
    0
   , -- EndClaimTransactionID
    0
   , -- DKPostingDateID
    0
   , -- DKEndPostingDateID
    0  -- RelativePrecedence
   ),
   VALUES  (1
   , -- PracticeID
    1191
   , -- ClaimID
    12346
   , -- ClaimTransactionID
    0
   , -- InsurancePolicyID
    0
   , -- InsuranceCompanyPlanID
    0
   , -- PatientID
   0
   , -- LastAssignment
    1
   , -- Status
    '2012-07-04 16:56:49'
   , -- PostingDate
    '2012-07-04 16:56:49'
   , -- EndPostingDate
    NULL
   , -- LastAssignmentOfEndPostingDate
    0
   , -- EndClaimTransactionID
    0
   , -- DKPostingDateID
    0
   , -- DKEndPostingDateID
    0  -- RelativePrecedence
   )
   ,
   VALUES  (1
   , -- PracticeID
    1191
   , -- ClaimID
    12347
   , -- ClaimTransactionID
    0
   , -- InsurancePolicyID
    0
   , -- InsuranceCompanyPlanID
    0
   , -- PatientID
   0
   , -- LastAssignment
    1
   , -- Status
    '2012-07-05 16:56:49'
   , -- PostingDate
    '2012-07-05 16:56:49'
   , -- EndPostingDate
    NULL
   , -- LastAssignmentOfEndPostingDate
    0
   , -- EndClaimTransactionID
    0
   , -- DKPostingDateID
    0
   , -- DKEndPostingDateID
    0  -- RelativePrecedence
   )

您可能想查找一些可靠的資源（例如，f…手冊，或在 SQL 標準中），當確切地驗證了 CHECK 約束時。如果結果是“在處理完 INSERT 之後”，那麼您可能會嘗試弄清楚現有匹配行的計數如何永遠為零。

除此之外，大多數引擎不允許在 CHECK 約束中引用表。這是無可救藥的煩人，但有一個很好的理由！. 原因是，如果引用的表發生更新，也應該檢查 CHECK 約束，這會以某種方式影響聲明它的表的某些行的此 CHECK 約束的結果。

引擎通常不知道如何有效地做到這一點。因此，他們將這種限制強加給使用者，以便至少不會損害數據的完整性。當您發現帶有某些 SELECT 的 UDF 可以繞過限制時，不要誤以為您很聰明。你只是在自欺欺人。幾個月後，您將成為下一個提出另一個“CHECK 約束不起作用”問題的人。

這條線說不能LastAssignment打開：

CHECK (dbo.FN_IsLastAssigned(ClaimId)=0) 

應該是：

CHECK (dbo.FN_IsLastAssigned(ClaimId)<=1)

請注意，像這樣的檢查約束不會擷取update列LastAssignment。例如見這篇博文。您可以通過@LastAssignment作為參數傳遞來解決這個問題：

alter FUNCTION FN_IsLastAssigned(@ClaimId INT, @LastAssignment bit)
...
CHECK (dbo.FN_IsLastAssigned(ClaimId, LastAssignment)<=1)

但歸根結底，SQL Server 並不是為了支持這些檢查約束而編寫的。我覺得最好完全避免它們。

引用自：https://dba.stackexchange.com/questions/20467