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

If you used a sequence to generate the pk you can use select currval from dual to return the value of the PK.

Hth,

--
Sybrand Bakker, Oracle DBA
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 Fri Feb 04 2000 - 11:45:01 CST