ArmorCode ArmorCode - 1 year ago 50
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

table that allows
to be NULL if
Payment_Total = 0
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 Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download