Re: how to get value of auto-increment PK of row just inserted?

From: Otis Gospodnetic <otis_at_my-deja.com>
Date: Tue, 08 Feb 2000 22:47:03 GMT
Message-ID: <87q6d5$831$1@nnrp1.deja.com>

Hi,

In article <949686432.18196.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postmaster_at_sybrandb.nospam.demon.nl> wrote:
> If you used a sequence to generate the pk you can use select currval
from
> dual to return the value of the PK.
> Sybrand Bakker, Oracle DBA

Thanks, I think this helps.
However, any idea why this is happening:

1. why can't I do "select user_id_seq.currval from dual" before doing "select user_id_seq.nextval from dual" ?
2. why does the sequence increase when I do simple selects such as "select user_id_seq.nextval from dual" ? Is this normal?

For example:

SQL> select user_id_seq.currval from dual; select user_id_seq.currval from dual

                                *

ERROR at line 1:
ORA-08002: sequence USER_ID_SEQ.CURRVAL is not yet defined in this session

SQL> select user_id_seq.nextval from dual;

   NEXTVAL

---

       429

SQL> select user_id_seq.nextval from dual;

   NEXTVAL

---

       430

Thanks,

Otis

> Otis Gospodnetic <otis_at_my-deja.com> wrote in message
> news:87ev4a$cn7$1_at_nnrp1.deja.com...
> > Hi,
> >
> > This must be a basic question. I have 2 tables (examples below)
where
> > the second one has a FOREIGN KEY that references a PRIMARY KEY of
the
> > first table.
> >
> > This PRIMARY KEY (user_id column, see below) has a trigger set on
it to
> > auto-increment user_id by +1 on insert.
> >
> > My question is:
> > When I want to insert a new row in the second table, how do I know
what
> > user_id to use? In other words, how do I get the user_id of the row
I
> > just inserted in the first table?
> >
> > For example:
> >
> > INSERT INTO user_auth (username,password) VALUES ('u', 'p');
> >
> > <Now how do I get user_auth.user_id of the row that was just
inserted?>
> > <I need it for the second insert, below>
> >
> > INSERT INTO user_info (user_id,name) VALUES (????,'Mali Medo');
> >
> > Thanks,
> >
> > Otis
> > P.S.
> > Example tables:
> >
> > create table user_auth
> > (
> > user_id int
> > CONSTRAINT pk_user_id PRIMARY KEY,
> > username varchar(32) NOT NULL,
> > password varchar(16) NOT NULL,
> > hint varchar(128)
> > );
> >
> > create table nm_user_info
> > (
> > user_id int,
> > FOREIGN KEY (user_id) REFERENCES
> > user_auth(user_id) ON DELETE CASCADE,
> > name varchar(128)
> > );

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Feb 08 2000 - 16:47:03 CST