SQL Server条件检查约束 [英] SQL Server conditional CHECK constraint

问题描述

我正在使用SQL Server 2008 Management Studio。以下是我必须写的内容，对于第二个约束，我遇到了一些困难。这有点让我感到困惑，我真的很感谢您的帮助。

I'm using SQL Server 2008 Management Studio. Below is what I have to write, and I'm having some difficulties for the second constraint. It is a little bit confusing me and I would really appreciate some help.

编写一条ALTER TABLE语句，该语句将两个新的检查约束添加到AP数据库的发票表。第一个应该允许（1）仅当PaymentTotal为零时，PaymentDate为空；以及（2）仅当PaymentTotal大于零时，PaymentDate不为空。第二个约束应防止PaymentTotal和CreditTotal的总和大于InvoiceTotal。

Write an ALTER TABLE statement that adds two new check constraints to the Invoices table of the AP database. The first should allow (1) PaymentDate to be null only if PaymentTotal is zero and (2) PaymentDate to be not null only if PaymentTotal is greater than zero. The second constraint should prevent the sum of PaymentTotal and CreditTotal from being greater than InvoiceTotal.

这是我到目前为止的内容，第一个约束有效，但第二个无效（PaymentTotal和CreditTotal的总和大于InvoiceTotal）。

Here is what I have so far, the first constraint works but not the second, (sum of the PaymentTotal and CreditTotal from being greater than InvoiceTotal).

ALTER TABLE Invoices WITH CHECK
ADD check (
    (PaymentTotal = 0 AND PaymentDate is NULL)
    OR
    (PaymentTotal > 0 AND PaymentDate is NOT NULL)
)
ADD CHECK (
    (PaymentTotal < InvoiceTotal = SUM)
    OR
    (CreditTotal < InvoiceTotal = SUM)
)

先谢谢您。

推荐答案

您已经写了一个汇总没有参数的函数（ SUM（））。

You have written an aggregate function (SUM()) with no parameters.

第二个约束应防止PaymentTotal和CreditTotal的总和大于InvoiceTotal。这让我有些困惑，但是您应该将其更改为：

"The second constraint should prevent the sum of PaymentTotal and CreditTotal from being greater than InvoiceTotal." This is a bit confusing to me, but here's what you should change it to:

ALTER TABLE Invoices WITH CHECK 
ADD check ( 
    (PaymentTotal = 0 AND PaymentDate is NULL) 
    OR 
    (PaymentTotal > 0 AND PaymentDate is NOT NULL) 
) 
go

ALTER TABLE Invoices WITH CHECK
ADD CHECK ( 
    (PaymentTotal + CreditTotal) <= InvoiceTotal 
) 
go

这篇关于SQL Server条件检查约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！