ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How to use SQL Constraint logic

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to use SQL Constraint logic

    I'm trying to expand my horizons, so to speak.

    The ability to put some of the data validation into the database using CHECK constraints and with triggers. How do I use this in the RPG code? If I have a series of CHECK constraints, how do I determine which constraint it didn't like, and what the valid values are for purposes of providing feedback to the user?

    For example:

    Code:
    CREATE TABLE SYTTAPSCH (
        TAPVOL CHAR(6) CCSID 37 NOT NULL DEFAULT '' ,
        TAPDCODE NUMERIC(1, 0) NOT NULL DEFAULT 0 ,
        TAPWCODE NUMERIC(1, 0) NOT NULL DEFAULT 0 ,
        TAPBTYPE CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
            TAPSAVTS TIMESTAMP DEFAULT CURRENT TIMESTAMP,
            TAPLSTUPD TIMESTAMP IMPLICITLY HIDDEN NOT NULL DEFAULT
                      CURRENT TIMESTAMP,
        CONSTRAINT Q_QGPL_SYTTAPSCH_TAPVOL_00001 PRIMARY KEY( TAPVOL ),
            CHECK (TAPDCODE BETWEEN 1 AND 7),
            CHECK (TAPWCODE BETWEEN 1 AND 2),
            CHECK (TAPBTYPE IN ('F','D')))
        RCDFMT TAPSCH     ;
    To utilize the CHECK above I would have to first update or write the data to the table. Let's say that the program is trying to update the table with TAPDCODE value of 8. It will fail, how do I programmatically determine which constraint rejected the update and perhaps the criteria used in the constraint?

    Or is the preferred method of putting the validation logic at the Database (DB) level using triggers? I assume you'd use SIGNAL and specify a different value to flag what went wrong??????

  • #2
    I am not sure, but I think this bit of your code:
    Code:
        CONSTRAINT Q_QGPL_SYTTAPSCH_TAPVOL_00001 PRIMARY KEY( TAPVOL ),
            CHECK (TAPDCODE BETWEEN 1 AND 7),
            CHECK (TAPWCODE BETWEEN 1 AND 2),
            CHECK (TAPBTYPE IN ('F','D'))
    Is assigning the primary key and all the individual checks to be part of a single constraint called Q_QGPL_SYTTAPSCH_TAPVOL_00001. Either that, or the three checks will have constraint names generated by the SQL engine.

    What you can do, is give each check its own constraint name:
    Code:
        CONSTRAINT Q_QGPL_SYTTAPSCH_TAPVOL_00001 PRIMARY KEY( TAPVOL ),
            CONSTRAINT SYTTAPSCH1 CHECK (TAPDCODE BETWEEN 1 AND 7),
            CONSTRAINT SYTTAPSCH2 CHECK (TAPWCODE BETWEEN 1 AND 2),
            CONSTRAINT SYTTAPSCH3 CHECK (TAPBTYPE IN ('F','D'))
    Now, if an SQL insert/update fails, then you can use get diagnostics to get the name of the failed constraint:
    Code:
       if SQLSTT = '23513'; //23513 is the SQLSTT for a failed constraint check in an insert/update
           exec sql get diagnostics condition 1
             :cnsname1 = CONSTRAINT_NAME
       endif;
    So if it failed because TAPBTYPE was not F or D, then cnsname1 would be set to 'SYTTAPSCH3'

    Disclaimer - I have not used get diagnostics very much. Using get diagnostics for condition, there can be multiple conditions indexed by that number 1, I'm not sure

    The GET DIAGNOSTICS statement obtains information about the previous SQL statement that was executed.

    Comment


    • #3
      It looks like the system assigns names to the constraints (create the table with original example, then use WRKPFCST).. It looks like I would be able to use your code by just testing the first two characters of SQLSTT to be 23. That way it works not only for the check constraint but the Primary Key as well....or any other type of constraint I may add down the road.

      Comment


      • #4
        Thank you for you insight! It was very helpful!!!

        Comment


        • #5
          You're welcome

          Please let me know how this goes, I have played around with get diagnostics but I have never used it in anger, so to speak.

          Comment


          • #6
            I'm assuming the actual constraints would be configurable and not hard-coded.

            Comment


            • #7
              Originally posted by Jim Suiter View Post
              I'm assuming the actual constraints would be configurable and not hard-coded.
              That would be ideal - but I haven't figured out how to accomplish that yet. Can you point me in the right direction?

              Comment


              • #8
                You can add or remove constraints in an ALTER TABLE statement

                A check constraint ensures the validity of data during insert and update operations by limiting the allowed values in a column or group of columns.


                This only shows adding them, but the same method applies for dropping them too.

                That is about as configurable as they get I think.

                Comment


                • #9
                  Vector,

                  Yes. But that's still "hard-coded" because you can't add or delete the constraints when the file is used. Ideally there would be a more dynamic way to do it - which I'd guess really requires trigger program(s) rather than constraints, which is fine but I still want to know best practices in this logic.

                  Rocky

                  Comment


                  • #10
                    After doing some web searches I think it might be possible to use a UDF for a softcoded constraint.

                    Code:
                    CHECK (myFunction(TAPDCODE) = '1')
                    
                    CREATE FUNCTION myFunction ( @arg ) RETURNS CHAR(1)
                    AS
                    BEGIN
                      IF EXISTS (SELECT * FROM AllowedValuesTable WHERE allowedvalue = @arg)
                        return '1';
                      return '0';
                    END;
                    
                    CREATE TABLE AllowedValuesTable  ( allowedvalue int )
                    
                    To make TAPDCODE 1, 3, or 7 valid:
                    INSERT INTO AllowedValuesTable VALUES (1), (3), (7)

                    Comment


                    • #11
                      I create a function and when trying to use it in the CHECK constraint it complains that I'm using a user-defined function....

                      Comment


                      • #12
                        Originally posted by Rocky View Post
                        I create a function and when trying to use it in the CHECK constraint it complains that I'm using a user-defined function....
                        That's not possible with a constraint. If you want to check data in this way you need a BEFORE INSERT/UPDATE trigger.
                        Birgitta

                        Comment


                        • #13
                          Birgitta,

                          I agree, but what is the best practice? I keep hearing people preach putting the logic at the DB level, which I can see the benefit but if I have a number of fields that I'm checking, how do I communicate back what field failed the test?

                          Comment


                          • #14
                            By having a separate named constraint for each field, then name of the constraint that failed (from get diagnostics) tells you which field failed?

                            Comment


                            • #15
                              But to have it dynamic, CHECK constraints don't work. Requires a trigger. I can execute SIGNAL specifying a different SIGNAL for each field, but not sure how to pass back any message / text.

                              Comment

                              Working...
                              X