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?


Recommended Answers

All 3 Replies

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.

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

Never use SELECT MAX(id) FROM whatevertable

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.