Re: Duplicate check constraints on the same column

At the risk of stating the obvious, have you made sure you don't have two copies of the table in different schemas? Since you are selecting off all_constraints and not limiting the query by/selecting owner, this could be a possibility.. It might be worth to select the owner as well... You never know, may be it really is that easy :)

Regards,

Jonas Plumecocq

Aram Meguerian wrote:

> May be (guess work here) when you imported the database
> Oracle created all these checks constraints. I think this
> can happen if the constraints are not properly named and
> are create with the Oracle default names but I am not sure
> since I use named constraints whenever I can and I haven't
> noticed no such thing.
>
> Does anybody else know anything about it ?
>
> --
>
> Aram Meguerian
> aram_at_unisys.com.br
>
> -------------------------------------------------------------------
> TANSTAAFL - There ain't no such thing as a free lunch
> by Robert A. Heinlein
> -------------------------------------------------------------------
> I don't work at Unisys, it is just my Internet Provider,
> so don't blame it for anything I have just said.
> -------------------------------------------------------------------
>
> Limin Guo <guo_at_andrews.edu> escreveu no artigo <Pine.SOL.3.91.971120134427.12601A-100000_at_deepika.squonk.net>...
> > On one of my development Oracle server (Oracle 7.3.3 on Windows NT 4.0)
> > we found something interesting: duplicate check constraints on the
> > same columns. Following is an example:
> >
> > SQL> select constraint_name, search_condition
> > 2 from all_constraints
> > 3 where table_name = 'C_CLIENT_REFERRALS'
> >
> > CONSTRAINT_NAME SEARCH_CONDITION
> > --------------------------- -------------------------------------------
> > CCR_CC_PK
> > CCR_PRG_FK
> > .
> > .
> > SYS_C0011051 cc_client_id BETWEEN 1 AND 999999
> > SYS_C0011052 prg_program_id BETWEEN 'A' AND 'ZZZZZ'
> > SYS_C0011053 to_from_flag IN ('T', 'F')
> > .
> > .
> > SYS_C00918 cc_client_id BETWEEN 1 AND 999999
> > SYS_C00919 prg_program_id BETWEEN 'A' AND 'ZZZZZ'
> > SYS_C00920 to_from_flag IN ('T', 'F')
> >
> > This perticular database was created months ago and fully imported from
> > the other one. We don't have any duplicate check constraints on the original
> > one. Also this doesn't look to me like someone created those check constraints
> > manually because of the default constraint names. This problem happened
> > on some other tables as well.
> >
> > How could it happen?
> >
> > Thanks for any clues and suggestions.
> >
> > Regards,
> >
> > Limin.
> >
Received on Thu Dec 04 1997 - 00:00:00 CST