Re: constraints question
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: daniel.fink@xxxxxxxxxxxxxx
- Date: Thu, 30 Apr 2009 23:16:59 +0200
To complement Daniel's comment, if your goal is simply to get rid of
self-referencing tables, you can add a condition saying that
table_name <> prior table_name
in the CONNECT BY clause - It will break the recursion.
However, you can have other cycles than self-references. You shouldn't,
but sometimes you have some kind of weird "ménage à trois" in the
relationships of your schema (just wish that everything were properly
designed ...). In such a case, NOCYCLE is mandatory.
If you are on Oracle 9, use the condition above and pray. If you are on
Oracle 10, add NOCYCLE for safety.
HTH
S Faroult
Daniel Fink wrote:
> If you are on 10g, add the NOCYCLE option to avoid this situation. You
> should also add CONNECT_BY_ISCYCLE to check the constraint that is
> self-referencing. More can be found in the 10g SQL Reference manual
> (quoted below)
>
> "The |NOCYCLE| parameter instructs Oracle Database to return rows from
> a query even if a |CONNECT| |BY| |LOOP| exists in the data. Use this
> parameter along with the |CONNECT_BY_ISCYCLE| pseudocolumn to see
> which rows contain the loop. Please refer to CONNECT_BY_ISCYCLE
> Pseudocolumn
> <mailbox:///C%7C/Home/Netscape%20Mail/daniel.fink_optimaldba.com/Trash?number=1033604&part=1.1.2&filename=pseudocolumns001.htm>
> for more information."
>
> Regards,
> Daniel Fink
>
> --
> Daniel Fink
>
> OptimalDBA.com - Oracle Performance, Diagnosis, Data Recovery and Training
>
> OptimalDBA http://www.optimaldba.com
> Oracle Blog http://optimaldba.blogspot.com
>
> Lost Data? http://www.ora600.be/
>
>
> Eugene Pipko wrote:
>>
>> Thanks Jack,
>>
>> Query fails with ORA-01436: CONNECT BY loop in user data.
>>
>> I believe it is because there is a self-referencing constraint on SALES
>>
>>
>>
>> Eugene Pipko
>>
>> Seattle Pacific Industries
>>
>> office: 253.872.5243
>>
>> cell: 206.304.7726
>>
>> P Please consider the environment before printing this e-mail.
>>
>>
>>
>> *From:* jack.van.zanen@xxxxxxxxx [mailto:jack.van.zanen@xxxxxxxxx]
>> *On Behalf Of *Jack van Zanen
>> *Sent:* Wednesday, April 29, 2009 9:37 PM
>> *To:* Mark.Bobak@xxxxxxxxxxxx
>> *Cc:* Eugene Pipko; oracle-l-freelists
>> *Subject:* Re: constraints question
>>
>>
>>
>> try this
>>
>>
>>
>>
>>
>> select
>>
>> lpad(' ',2*(level-1)) ||table_name from
>>
>> (
>>
>> select a.table_name , b.table_name parent
>>
>> from user_constraints a left outer join user_constraints b on
>> (a.r_constraint_name=b.constraint_name)
>>
>> )
>>
>> start with table_name='SALES'
>>
>> connect by prior table_name=parent
>>
--
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
