Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    17

    Unanswered: Constraint with row selection, depending on conditions

    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.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    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).

  3. #3
    Join Date
    Jul 2009
    Posts
    17
    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

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example in "DB2 SQL Rererence"

    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%’

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •