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)

what error is being returned? What platform and verison of DB2? — Charles, Dec 05 '16 at 20:09
10.5 DB2 is the version and the error isDB2 SQL Error: SQLCODE=-544, SQLSTATE=23512, SQLERRMC=START_END_NOTNULL, DRIVER=4.16.53 [SQL State: 23512] — Brendan Robert, Dec 05 '16 at 20:17
That error indicates that an existing row doesn't meet the constraint. — Charles, Dec 05 '16 at 20:56
this query returns null though select * from pluspgbtrans where eip_linestatus = 'RELEASED' and eip_endate is null and eip_startdate is null — Brendan Robert, Dec 05 '16 at 22:31

score 0 · Accepted Answer · answered Dec 06 '16 at 00:34

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
        )
    );

check constraint that references multiple columns in the same table

1 Answers1