Duplicated Check Constraints
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Duplicated Check Constraints

  1. #1
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Hi all,

    Friday I did an import (high import) that failed sometimes but finally runs "correctly". Now I have detected that there are some tables with duplicated check constraints and I want to know all duplicated check constraints on the database to drop it.

    Does anybody how to do it?

    Thanks in advance and best regards

    Angel

    P.S. Database Oracle 8.1.7.1.1 on Windows NT

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    cant you check user_constraints.SEARCH_CONDITION?

  3. #3
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Yes, but the problem is this check constraint haven´t name, only sys00xxxx... Then is difficult to know wich are duplicated.

    Thanks anyway Pando




  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    quoted from asktom

    Code:
    create function search_cond( p_cons_name in varchar2 ) return varchar2
    as
       l_search_condition varchar2(4000);
    begin
       select SEARCH_CONDITION into l_search_condition
         from user_constraints
        where constraint_name = p_cons_name;
    
       return l_search_condition;
    end;
    /
    
    and then
    
    select search_cond_vc, count(*) 
    from (
    select search_cond( constraint_name ) search_cond_vc
      from user_constraints
     where constraint_name like 'SYS%'
         )
    group by search_cond_vc
    having count(*) > 1;
    The reason of using a function is to convert long to varchar2 otherwise we cannot compare data

  5. #5
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Thank you very much Pando. I will try it.

    Regards

    Angel

  6. #6
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Hi again,

    The result of the above query is the search_condition and the number of times it is duplicated, but it not tell the table_name ...

    Do you know how could I compare with a long type:

    select owner,constraint_name,constraint_type,table_name
    from user_constraints
    where search_condition like 'ANO IS NOT NULL';

    Where ANO IS NOT NULL is the SEARCH_CONDITION from USER_CONSTRAINTS.

    Thanks in advance and best regards

    Angel

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Code:
    select table_name
    from user_constraints where
    search_cond(constraint_name)='"ANO" IS NOT NULL';

  8. #8
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Thanks for your quickly reply Pando, but it doesn't work:

    SQL> select table_name
    2 from user_constraints where
    3 search_condition='"ANO" IS NOT NULL';
    search_condition='"ANO" IS NOT NULL'
    *
    ERROR en línea 3:
    ORA-00997: uso no válido del tipo de datos LONG
    (invalid use of datatype LONG)

    (I think you understand spanish no? :-) )

    Regards

    Angel

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    it works if you sue the function I posted yesterday and execute the query I posted

    example:

    Code:
    SQL> r
      1  select table_name, search_cond( constraint_name ) search_cond_vc
      2    from user_constraints
      3*  where constraint_name like 'SYS%'
    
    TABLE_NAME                     SEARCH_COND_VC
    ------------------------------ ----------------------------------------
    CON                            "OWNER#" IS NOT NULL
    CON                            "NAME" IS NOT NULL
    CON                            "CON#" IS NOT NULL
    EMP                            "EMPNO" IS NOT NULL
    MD_EJECUCION_PROCESO           "ID_EJECUCION_PROCESO" IS NOT NULL
    MV_FASTDEPTEMP                 "EMPNO" IS NOT NULL
    MV_TEST                        "EMPNO" IS NOT NULL
    
    SQL>
      1  select table_name, search_condition
      2  from user_constraints where
      3* search_cond(constraint_name)='"EMPNO" IS NOT NULL'
    
    TABLE_NAME                     SEARCH_CONDITION
    ------------------------------ --------------------------------------------------------------------------------
    EMP                            "EMPNO" IS NOT NULL
    MV_FASTDEPTEMP                 "EMPNO" IS NOT NULL
    MV_TEST                        "EMPNO" IS NOT NULL
    spanish yes

  10. #10
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Thanks again Pando. It works fine ;-)

    Regards

    Angel

Posting Permissions

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



Click Here to Expand Forum to Full Width