Duplicated Check Constraints

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

cant you check user_constraints.SEARCH_CONDITION?

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

Thanks anyway Pando

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

Thank you very much Pando. I will try it.

Regards

Angel

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

Code:

---

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

---

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

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 :o

Thanks again Pando. It works fine ;-)

Regards

Angel