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

From: Otis Gospodnetic <otis_at_my-deja.com>
Date: Fri, 04 Feb 2000 16:35:22 GMT
Message-ID: <87ev4a$cn7$1@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 - 10:35:22 CST