the primary key of a row that I have just inserted

[handro1104]

How do I set the foreign key in a table to the primary key of a row that I have just inserted when the primary key is created with autoincrement. One possibility is to select the maximum value of the primary key and use the next value, but this may fail if multiple processes are accessing the database and besides how can I be sure that primary keys are always incremented by one? If the solution is vendor dependent, please give me the details or links.

Thank you very much for your help.

[edited by: Woz at 10:45 am (utc) on Nov. 17, 2005]
[edit reason] No email addresses please, see TOS. [/edit]

[charlier]

If your using php/mysql you can use:

mysql_insert_id

(PHP 3, PHP 4, PHP 5)
mysql_insert_id -- Get the ID generated from the previous INSERT operation
Description
int mysql_insert_id ( [resource link_identifier] )

Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.

[chrisjoha]

I guess you could do something like this (MySQL autonumbering):

INSERT INTO table1 (pri_field, field2, field3, field4) VALUES (null, 'some value', 'other value', null);

INSERT INTO table2 (sec_key, field, otherfield) VALUES ((SELECT max(pri_field) FROM table1 WHERE field2 = 'some value' AND field3 = 'other value' AND field4 is null), 'bleh', 'bleh bleh');

And I'm sure someone else has a better suggestion too :)

[chrisjoha]

...and there you have it, charlier beat me to it. And probably with a better solution too. The only thing I'm wondering about is the case with a big push of inserts. How will mysql_insert_id help you in the (little likely) case that after your first insert and before the insert to the second table someone else makes an insert on the first table? Maybe transactions? If you start a transaction and insert, will mysql_insert_id get the last id from the insert in the current transaction?

[mukhtar2t]

peace be upon you

you can use this as the guys said :

INSERT INTO tbl (auto,text) VALUES(NULL,'text');
INSERT INTO tbl2 (id,text) VALUES(LAST_INSERT_ID(),'text');

and you can check this link :
[dev.mysql.com...]

[charlier]

Yes the manual says:

Parameters

link_identifier

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If by chance no connection is found or established, an E_WARNING level warning is generated.

However its even more reliable then that might imply:

Here's the relevant quote from the manual on LAST_INSERT_ID() which is located here: [dev.mysql.com...]

"The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions."

[charlier]

WOW! Guys here are quick with their fingers.

[handro1104]

Thank you very much to all. Is this solution appropriate to all vendors? Would the value I get from LAST_INSERT_ID be correct if it is used in a transaction when several processes are trying to do the same thing?

[mukhtar2t]

yes it will be relible if and only if you used a seperate connection like this :
$db = connect(); // function that connects to db
mysql_query("some sql statments",$db);
mysql_insert_id($db);

because this retrive the last insert id by this link identefier.