Duplicated Check Constraints

[aarroyob]

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

[pando]

cant you check user_constraints.SEARCH_CONDITION?

[aarroyob]

Yes, but the problem is this check constraint haven´t name, only sys00xxxx... Then is difficult to know wich are duplicated.

Thanks anyway Pando

[pando]

quoted from asktom

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

[aarroyob]

Thank you very much Pando. I will try it.

Regards

Angel

[aarroyob]

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

[pando]

select table_name
from user_constraints where
search_cond(constraint_name)='"ANO" IS NOT NULL';

[aarroyob]

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

[pando]

it works if you sue the function I posted yesterday and execute the query I posted

example:

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

[aarroyob]

Thanks again Pando. It works fine ;-)

Regards

Angel