This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Apr 3, 2013 6:53 AM by padders RSS

Fool question about constraint check

mcardia Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points