Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    10

    Unanswered: Check constraint

    I am once again having trouble as the text book for my class is not very good at explaining how else a check constraint is used. What I am trying to do is to ADD a check constraint. It needs to allow the payment_date column to be null only if payment_total is equal to 0 AND payment_date NOT NULL only if payment_total > 0.

    I keep wanting to write an if statement, but do not know if it is allowed. The book only shows how to check a single definition
    Code:
    ADD CONSTRAINT invoice_total_ck CHECK (invoice_total >= 0)
    This obviously gives me an error:

    Code:
    ALTER TABLE invoices
    ADD CONSTRAINT payment_date_ck CHECK (if payment_total = 0 then payment_date IS NULL OR if payment_total >0 then payemnt_date IS NOT NULL);
    Can I check with an IF/THEN?
    Last edited by C_J_GO; 06-19-11 at 13:11. Reason: Spelling

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    ALTER TABLE invoices
       ADD CONSTRAINT payment_date_ck 
       CHECK ( 
               (payment_total = 0 AND payment_date IS NULL) OR 
               (payment_total > 0 AND payment_date IS NOT NULL)
             );

  3. #3
    Join Date
    Jun 2011
    Posts
    10
    Thank you. Makes sense now!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •