I'm entering garbage data for my initial tests, so the two queries below
will return null. If the two check constraints pass, the insert will
ultimately fail as the FK constraints won't be satisfied.
My question is why does the first constraint succeed, but the second one
fails?
A second question would be whether I need to coalesce the new and old
values; I don't know which ones are used by default in a check
constraint.

/* succeeds, even though select statement won't return any rows */
ALTER TABLE CLAIM_INVOICEITEM ADD CONSTRAINT C_CLAIMINVOICEITEM_DECQTY
CHECK (
(SELECT FIRST 1
DECIMALQTY
FROM
CLAIMSPAIDREVERSED cpr
WHERE cpr.RXCLAIMNBR=RXCLAIMNBR AND cpr.CLMSEQNBR=CLMSEQNBR AND
cpr.CLAIMSTS=CLAIMSTS
) * QTY_CLAIM > 0
);

/* fails, select statement doesn't return any rows */
ALTER TABLE CLAIM_INVOICEITEM ADD CONSTRAINT C_CLAIMINVOICEITEM_SHIPQTY
CHECK (
(SELECT
ii.QTY - SUM(cii.QTY_SHIP)
FROM
CLAIM_INVOICEITEM cii
JOIN INVOICE_ITEM ii ON cii.INVOICEITEM = ii.ID
WHERE ii.ID = COALESCE(NEW.INVOICEITEM,OLD.INVOICEITEM)
GROUP BY
ii.ID, ii.QTY
) >= COALESCE(NEW.QTY_SHIP,OLD.QTY_SHIP)
);

Actually, it may have to do with the COALESCE. I added it to the three
values in the where clause for C_CLAIMINVOICEITEM_DECQTY and now that
check statement fails also. I got over a 170 hits on 'check constraint'
on the IBPhoenix site, so hopefully I'll get a reply before I exhaust
that resource.

Rick DeBay
Senior Software Developer
RxStrategies.net