Sorry for the constant posts, but I'm the only technical person here
this week so I don't have anyone to bounce these questions off of.
To answer my own question, I have to test for the null case as well.
Why the first test that didn't have COALESCE even worked I don't know.
Perhaps the check constraint wasn't run because FB knew that it was a
new row?

If anyone has a check constraints for dummies, I'd be happy to read it
(Yes, I have the The Firebird Book but mainly what it says is don't let
check constraints span tables...).

Rick DeBay

-----Original Message-----
From: Rick DeBay
Sent: Monday, January 24, 2005 4:24 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] More check constraint questions


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



Yahoo! Groups Links