1 2 Previous Next 25 Replies Latest reply: Apr 3, 2013 8:53 AM by padders RSS

    Fool question about constraint check

    mcardia
      Is there a way to check a value against a constraint check without do a insert/update?

      Exemple:
      ---constraint my_check ( check mycolumn in ('Y', 'N') )
      
      if check_constraint('X', 'my_check') then
           dbms_output.put_line('OK');
      else
           dbms_output.put_line('NOT OK');
      end if;
      I Know i can create a function with a exception to do this, but i ask if there is something native from oracle.

      Thanks.
        • 1. Re: Fool question about constraint check
          Ramin Hashimzadeh
          what is problem do samething like this?
          SELECT 1
          into yes_no
          FROM all_constraints
          where ....
          why you search standart function?
          • 2. Re: Fool question about constraint check
            mcardia
            I receive some data from a foreign system (web service).

            I want to validade the data and response with a message error that means something to sender, and not a generic message.

            I could do it hardcode like this:
            if your_data not in ('C', '3, 'P', 'O') then
                    response('your data is invalid beacause it is not a friend of R2D2');
            end if;
            That works, but it is not like i want to do. I don´t like anything hardcoded. In my example, ('C', '3, 'P', 'O') is already defined in my column check constraint. And i validate before any insert/update.
            So, Why not check against it and avoid duplicated code?
            That way, if sometime in the future i need to accept a diferent value, i just change the contraint and i do not need do search all my pl/sql to change anywhere that do this validation.

            So... you asked why and i´m asking why not? It´s look like something that should exist native.

            Thanks
            • 3. Re: Fool question about constraint check
              jeneesh
              But I am confused..

              If you are validating the data upfront -Why do you want Check constraint? Why do you want to do the same job twice?


              Or to put in another way - Check constraint is supposed to do the validation, then why are you doing it upfront?

              And if it is a requirement to do the check upfront - remove the check constraint. Have functions to do the validation.
              • 4. Re: Fool question about constraint check
                Ramin Hashimzadeh
                ok. i understand... you can catch exception check contracint and raise your own error :
                create table TTABLE1
                (
                  col1 INTEGER,
                  col2 INTEGER
                );
                
                -- Create/Recreate check constraints 
                alter table TTABLE1
                  add constraint MYCONS
                  check (COL1 IN (1,2,3));
                
                declare 
                  -- Local variables here
                  i integer;
                BEGIN
                  INSERT INTO TTABLE1 VALUES (4,2);    
                  COMMIT;
                EXCEPTION  
                  WHEN OTHERS THEN 
                    BEGIN
                      IF (SQLCODE = -2290) THEN
                         RAISE_APPLICATION_ERROR(-20001,'THIS IS MY ERROR');         
                      END IF;
                    END;
                end;
                ----
                Ramin Hashimzadeh
                • 5. Re: Fool question about constraint check
                  mcardia
                  It´s a integration with a ERP.

                  My package/procedures do the integration.

                  I can not change de database. the constraint is already there.
                  I´m trying to avoid duplicate work. If the ERP changes. My code is ready for it.

                  Sometimes we simplified the question here. I does not means the system or what we are trying to do is simple or we control everything.
                  There is a lot of things left behind that only the programer that is asking know about.
                  If we add every single detail in the question, it will become a book.

                  I´t kind of a yes/no question. Is there a native way to do this in pl/sql or I need to create my own function?

                  thanks
                  • 6. Re: Fool question about constraint check
                    Ramin Hashimzadeh
                    I´t kind of a yes/no question. Is there a native way to do this in pl/sql or I need to create my own function?
                    I show you one method in my last post
                    • 7. Re: Fool question about constraint check
                      jeneesh
                      In my knowledge, there is no direct way..

                      May be you could try to take out the check constraint condition from the standard DBA_/ALL_ views and use dynamic SQL to do the check..
                      • 8. Re: Fool question about constraint check
                        jeneesh
                        Ramin Hashimzadeh wrote:
                        I´t kind of a yes/no question. Is there a native way to do this in pl/sql or I need to create my own function?
                        I show you one method in my last post
                        You could re-read the thread..
                        mcardia wrote:
                        Is there a way to check a value against a constraint check without do a insert/update?

                        I Know i can create a function with a exception to do this, but i ask if there is something native from oracle.

                        Thanks.
                        • 9. Re: Fool question about constraint check
                          Ramin Hashimzadeh
                          Is there a way to check a value against a constraint check without do a insert/update?
                          did not pay attention (((
                          • 10. Re: Fool question about constraint check
                            mcardia
                            Hi!

                            Ramin, i thought that.

                            But think about a table with 30, 40 fields. Almost all of them not allowing null. That is how my ERP looks like.
                            And some table changes constantly (and it´s not my call, i´m just the guy who has to solve the problem created by others doing the best i can).

                            Thats why i´m trying to avoid using a function with insert/update and catch the exception.

                            I think I will do something with dynamic sql working with the column SEARCH_CONDITION of the table/view all_constraint and some regular expression.

                            Any better idea?
                            • 11. Re: Fool question about constraint check
                              Dave Rabone
                              A function which catches an exception on insert won't necessarily do the job anyway, because there may be several constraints, and the supplied data may violate more than one of the constraints.

                              The function can only report one of the violations - AFAIK you cannot determine the order in which constraints are tested in general.
                              • 12. Re: Fool question about constraint check
                                Solomon Yakobson
                                Well, there is no easy way. What you could do:
                                create table tbl(
                                                 col varchar2(1)
                                                )
                                /
                                create or replace
                                  function f1(
                                              v_val varchar2
                                             )
                                  return varchar2
                                  deterministic
                                  is
                                      v_res varchar2(10);
                                  begin
                                      if v_val in ('Y','N')
                                        then
                                          v_res := 'OK';
                                        else
                                          v_res := 'Not OK';
                                      end if;
                                      return v_res;
                                end;
                                /
                                alter table tbl
                                  add flag generated always as (f1(col))
                                /
                                alter table tbl
                                  add constraint tbl_chk1
                                    check(
                                          flag = 'OK'
                                         )
                                /
                                Now:
                                SQL> insert
                                  2    into tbl(col)
                                  3    values('Y')
                                  4  /
                                
                                1 row created.
                                
                                SQL> insert
                                  2    into tbl(col)
                                  3    values('N')
                                  4  /
                                
                                1 row created.
                                
                                SQL> insert
                                  2    into tbl(col)
                                  3    values('X')
                                  4  /
                                insert
                                *
                                ERROR at line 1:
                                ORA-02290: check constraint (SCOTT.TBL_CHK1) violated
                                
                                
                                SQL> set serveroutput on
                                SQL> declare
                                  2      v_val varchar2(1);
                                  3  begin
                                  4      v_val := 'Y';
                                  5      dbms_output.put_line('Value ' || v_val || ' is ' || f1(v_val));
                                  6      v_val := 'X';
                                  7      dbms_output.put_line('Value ' || v_val || ' is ' || f1(v_val));
                                  8  end;
                                  9  /
                                Value Y is OK
                                Value X is Not OK
                                
                                PL/SQL procedure successfully completed.
                                
                                SQL>  
                                One issue though. Since we added a virtual column we will have to use column list in inserts. Otherwise:
                                SQL> insert
                                  2    into tbl
                                  3    values('Y')
                                  4  /
                                  into tbl
                                       *
                                ERROR at line 2:
                                ORA-00947: not enough values
                                
                                
                                SQL> 
                                It this is a problem, you could:
                                create table tbl(
                                                 col varchar2(1)
                                                )
                                /
                                create or replace
                                  function f1(
                                              v_val varchar2
                                             )
                                  return varchar2
                                  deterministic
                                  is
                                      v_res varchar2(10);
                                  begin
                                      if v_val in ('Y','N')
                                        then
                                          v_res := 'OK';
                                        else
                                          v_res := 'Not OK';
                                      end if;
                                      return v_res;
                                end;
                                /
                                create index tbl_idx
                                  on tbl(f1(col))
                                /
                                FBI we created adds a hidden column to table tbl:
                                SQL> select  column_name
                                  2    from  user_tab_cols
                                  3    where table_name = 'TBL'
                                  4      and hidden_column = 'YES'
                                  5  /
                                
                                COLUMN_NAME
                                ------------------------------
                                SYS_NC00002$
                                
                                SQL> 
                                No we can:
                                alter table tbl
                                  add constraint tbl_chk1
                                    check(
                                          SYS_NC00002$ = 'OK'
                                         )
                                /
                                We could also rename SYS_NC00002$ to some meaningful name. Anyway, now:
                                SQL> insert
                                  2    into tbl(col)
                                  3    values('Y')
                                  4  /
                                
                                1 row created.
                                
                                SQL> insert
                                  2    into tbl(col)
                                  3    values('N')
                                  4  /
                                
                                1 row created.
                                
                                SQL> insert
                                  2    into tbl(col)
                                  3    values('X')
                                  4  /
                                insert
                                *
                                ERROR at line 1:
                                ORA-02290: check constraint (SCOTT.TBL_CHK1) violated
                                
                                
                                SQL> set serveroutput on
                                SQL> declare
                                  2      v_val varchar2(1);
                                  3  begin
                                  4      v_val := 'Y';
                                  5      dbms_output.put_line('Value ' || v_val || ' is ' || f1(v_val));
                                  6      v_val := 'X';
                                  7      dbms_output.put_line('Value ' || v_val || ' is ' || f1(v_val));
                                  8  end;
                                  9  /
                                Value Y is OK
                                Value X is Not OK
                                
                                PL/SQL procedure successfully completed.
                                And now you don't need to provide column list for inserts:
                                SQL> insert
                                  2    into tbl
                                  3    values('Y')
                                  4  /
                                
                                1 row created.
                                
                                SQL> 
                                SY.
                                • 13. Re: Fool question about constraint check
                                  EdStevens
                                  jeneesh wrote:
                                  But I am confused..

                                  If you are validating the data upfront -Why do you want Check constraint? Why do you want to do the same job twice?


                                  Or to put in another way - Check constraint is supposed to do the validation, then why are you doing it upfront?

                                  And if it is a requirement to do the check upfront - remove the check constraint. Have functions to do the validation.
                                  Just to play devil's advocate, if you depend on the application to guarantee the validity of your data, what's to prevent using sqlplus to create invalid data?
                                  • 14. Re: Fool question about constraint check
                                    padders
                                    I take it you are aware that constraints can refer to multiple columns? So you need to validate a record against the tables constraints and not a value.

                                    How do you intend to supply a meaningful error message for constraints that are added or changed later and about which you know nothing?

                                    If the files in question are large this could potentially add a significant overhead to the load process, is that acceptable?

                                    Is it acceptable to load the parts of the file that are valid? If so the DML ERROR LOGGING feature may be useful.
                                    1 2 Previous Next