check constraint that references multiple columns in the same table

Question

I am attempting to add a constraint to a DB2 database that will check three columns. I am using a table that is an invoice table that includes start date end date quantity item price etc. for each line item on an invoice. I want to prevent allowing start and end date from being null when a column linestatus = RELELASED. Here is the alter statement that I have so far. My question is why won't this work? I have verified that this table does not have any current instances of all three of these checks.

alter table pluspgbtrans 
add constraint start_end_notnull 
Check (eip_linestatus = 'RELEASED' AND eip_endate is not null AND eip_startdate is not null)

Answer 1

Your SQL statement is valid.

However, your logic has an error: this check does not apply only if eip_linestatus = 'RELEASED'.

As written, your constraint is asserting that all rows must have eip_linestatus = 'RELEASED' AND eip_endate is not null AND eip_startdate is not null.

So, if any rows in your table have eip_linestatus with a value of anything other than RELEASED, you'll get the SQL0544N error when you try to add the constraint.

To create the constraint you're looking for, you need to handle the other state(s) for eip_linestatus. I can't guess what they are, so here's a potential generic option:

alter table pluspgbtrans 
    add constraint start_end_notnull check (
        (eip_linestatus <> 'RELEASED') 
        OR
        (
             eip_linestatus = 'RELEASED' 
             AND eip_endate is not null 
             AND eip_startdate is not null
        )
    );