0

All,

I want to insert data into 6 tables. One of those tables has a primary key field that auto-increments (let's call it ID). The other tables use that ID number as a foriegn key.

My question is, how do I know what the newly generated value of ID is in order to use it in the other tables? Do I have to insert into the first table then read back the value of ID?

Thanks,
Bill

4
Contributors
3
Replies
21
Views
4 Years
Discussion Span
Last Post by Admin ESSL UK
0

Database servers will provide a way to query the current ID. For example, in SQL Server you'd select one of the following:

select scope_identity();         -- Last identity generated explicitly in your scope
select ident_current('MyTable'); -- Last identity generated for the specified table

For MySql you might use something like this:

select last_insert_id();
select last_insert_id('MyTable');

Any programming API that supports database access should also provide a convenient (sometimes not so convenient) way of grabbing identities.

0
mysql_query("INSERT INTO whatevertable ('Bob', 'Smith')");
$id = mysql_insert_id();

Never use SELECT MAX(id) FROM whatevertable

Edited by pritaeas: Added markdown.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.