retrieving newly created primary keys

[SweetPeaEmma]

Hi There! I hope someone can help me out!

What is the best way to retrieve the primary key of a newly created field?

I need the primary keys of two tables, Customer and Address, in order to populate the relationship table, hasaddress, in order to maintain referential integrity. I am using JSPs and JavaBeans to populate the two entity tables with data input by a user through html forms. The entity tables are populating fine, however i am having problems with the relationship tables.

I have read a bit, and thought that perhaps the following statement would work for my purposes (i.e. very small user load):
SELECT cid FROM Customer ORDER BY cid DESC LIMIT 1;
SELECT aid FROM Address ORDER BY aid DESC LIMIT 1;

and the value these evaluate to could be entered into an update statement.
However, i am unsure as to how to embed this into an SQL update statement.

Can it be done, or does anyone know of a more efficient (and maybe accurate ) way of populating relationship tables correctly?

Thanks everyone!

 

[sleipnir214]

What I generally do (albeit in PHP) is:

Insert the first record.
Issue the "SELECT last_insert_id()" and remember that value.
Insert the second record.
Issue the "SELECT last_insert_id()" a seconds time and remember that second value.
Insert the relating record using the two values fetched previously.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!