RE: multitable inserts and sequences
- From: "Larry Elkins" <elkinsl@xxxxxxxxxxx>
- To: <thomas.kellerer@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 28 Jun 2013 08:56:13 -0500
Thomas,
Sounds like you gave some thought to how the currval and nextval would be
evaluated with respect to the order in which the inserts
may be processed. But this was still bugging me, seems like something I was
still forgetting (this was 5-7 years ago, and I was
helping someone with this, I'm a little fuzzy).
Take a look at
http://asktom.oracle.com/pls/asktom/f?p=100:11:8091295109158::::P11_QUESTION_ID:6915127515933,
and the comment
"because the nextval is evaluated (as documented) once per row from the
rowsource".
You can reference NEXTVAL on each of the two inserts, and they will both return
the same value. I did a test, referencing nextval on
both inserts and ended up with the exact same set of values in both tables. I
did it again with an insert into the parent, and *two*
inserts for the child table. And I got the "same" rows twice in the child,
matching the parent. By the same token, added another
column to the child table, defined it as the primary key, and used a second
sequence, and referenced it for this new PK column, and
using newseq.NEXTVAL on each of the two inserts into the child, immediately
threw a unique constraint violation.
I think, considering I favor natural keys over surrogate keys (not intended to
start that whole discussion <grin>) I may have nudged
the person toward not using sequences at all for this anyway. I've tried to
find my notes on this, no luck. But something else to
consider before you drop into PL/SQL for doing this.
Larry G. Elkins
elkinsl@xxxxxxxxxxx
Cell: 214.695.8605
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Thomas
> Kellerer
> Sent: Friday, June 28, 2013 12:55 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: multitable inserts and sequences
>
> Hello all,
>
> Larry Elkins, 28.06.2013 03:58:
>
> > In short, the order in which the tables are inserted is not determinant.
> > Take a look at Note: ID
> 265826.1, referencing bug 2891576.
> > So, if someone is considering this approach, the workaround is
> > deferred constraints (our choice when facing this), or, temporarily
> > disabling the constraint. The issue *wasn't* the sequence, it was the order
> > in which the inserts
> were processed. So, just a head's up to those considering it.
>
> Thanks for the answer. We will then keep on using PL/SQL and a loop that
> guarantees the sync between
> the sequence usage (even though there is no FK we do require the values to be
> "in sync".
>
>
> Jonathan Lewis, 27.06.2013 21:04:
> > I think the problem with this is that until it's documented to be
> > working as expected you can't guarantee that it will work in the future
> > however many times you test
> it now.
>
> Good point ;)
>
> Regards
> Thomas
>
> --
> //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
