title: SQL Server conditional CHECK constraint
body: <p>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.</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>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).</p>
  
  <pre><code>ALTER TABLE Invoices WITH CHECK
  ADD check (
      (PaymentTotal = 0 AND PaymentDate is NULL)
      OR
      (PaymentTotal &gt; 0 AND PaymentDate is NOT NULL)
  )
  ADD CHECK (
      (PaymentTotal &lt; InvoiceTotal = SUM)
      OR
      (CreditTotal &lt; InvoiceTotal = SUM)
  )
  </code></pre>
  
  <p>Thank you in Advance.</p>