Constraint with row selection, depending on conditions

dajense · #1 (**permalink**) 12-28-09, 07:23

Hi,

I need to specify a constraint, that will check on inserts and updates if the inserted value matches certain conditions and if so, the value has to be between two sums that are calculated from the same coloumn and their elements are selected by slightly different conditions. I thought of archieving this whith a check constraint like this:

Code:

ALTER TABLE DATA_VALUE
ADD CONTSTRAINT VALIDATE_VALUE
CHECK(
SELECT DATA_VALUE.VALUE WHERE .....

BETWEEN 'SELECT DATA_VALUE.VALUE WHERE ....'
AND 'SELECT DATA_VALUE.VALUE WHERE....'

But the check constraint doesn't seem to like the first select. So any ideas how else I could archieve this?

Thx.

tonkuma · #2 (**permalink**) 12-28-09, 09:34

You made syntax errors.

Seeing your requirements, ALTER statement might be .....

Code:

ALTER TABLE data_value
ADD CONSTRAINT validate_value
CHECK(
    (conditions for value)
AND value
    BETWEEN (SELECT SUM(value) FROM data_value WHERE ...)
        AND (SELECT SUM(value) FROM data_value WHERE ...)
)

or

Code:

ALTER TABLE data_value
ADD CONSTRAINT validate_value
CHECK(
NOT (conditions for value)
OR
    value
    BETWEEN (SELECT SUM(value) FROM data_value WHERE ...)
        AND (SELECT SUM(value) FROM data_value WHERE ...)
)

But, subqueries cannot be included in a CHECK constraint.

See, CREATE TABLE statement in DB2 SQL Reference.

Quote:

CHECK (check-condition)
Defines a check constraint. The search-condition must be true or unknown for every row of the table.

search-condition

The search-condition has the following restrictions:
v A column reference must be to a column of the table being created.
v The search-condition cannot contain a TYPE predicate.
v The search-condition cannot contain any of the following (SQLSTATE 42621):
– Subqueries
– XMLQUERY or XMLEXISTS expressions
– Dereference operations or DEREF functions where the scoped reference argument is other than the object identifier (OID) column
.....
.....

I thought that an alternative way is CREATE TRIGGER(s).

dajense · #3 (**permalink**) 12-28-09, 10:12

Thanks for pointing out the error and for your advice. I kind of had a feeling that subqueries don't work within the check constraint. But as I need additional functionalities I think triggers are the way to go

tonkuma · #4 (**permalink**) 12-28-09, 10:24

Here is an example in "DB2 SQL Rererence"

Quote:

Example 3: Create a trigger that will cause an error when an update occurs that
would result in a salary increase greater than ten percent of the current salary.

Code:

 CREATE TRIGGER RAISE_LIMIT
   AFTER UPDATE OF SALARY ON EMPLOYEE
   REFERENCING NEW AS N OLD AS O
   FOR EACH ROW
   WHEN (N.SALARY > 1.1 * O.SALARY)
         SIGNAL SQLSTATE ’75000’ SET MESSAGE_TEXT=’Salary increase>10&#37;’

Internet Services

Web Development

Digital Marketing

Consumer Tech