ArmorCode ArmorCode - 4 months ago 7
SQL Question

Alter Table add check for nulls and not nulls

I am using Oracle Express, and I'd like to make a statement to add a check constraint to my

Invoices
table that allows
Payment_Date
to be NULL if
Payment_Total = 0
, AND
Payment_Date
to be NOT NULL if
Payment_Total > 0
.

I only understand how to alter a table to add a constraint that checks that a column's value. I don't understand how to make constraints that Allow null values or disallow null values if a certain condition (ColumnValue > SomeValue) is met.

Answer

Here is how you can express the check constraint:

alter table t add constraint ck_values
    check ((payment_date is null and payment_total = 0) or
           (payment_date is not null and payment_total > 0)
          );