I like to let MySQL generate unique keys for most of my tables by making them numerical and using the AUTO_INCREMENT feature. It is both an easy and convenient way to deal with primary keys. It has one disadvantage though.
Since the key is generated upon insertion of a new row, you can never know its value before the SQL statement is executed. Furthermore, it can be problematic to insert a new row into a table, and then use its key as a foreign key in another table. One could potentially follow the insert statement with a selection, but unfortunately it is possible that the primary key is the only unique value that distinguishes several rows. We can never be a 100% sure that we are getting the right one, using a selection statement.
MySQL provides us with a nifty feature that does precisely what we need:
Lets say we have two tables tab1(id, value) and tab2(id, value, tab1_id). To insert a new row to tab1, and then new row to tab2 while referencing tab1 we can do this:
INSERT INTO tab1 (name) VALUES ('some value')
INSERT INTO tab2 (value, tab1_id) VALUES ('another value', LAST_INSERT_ID())
If you are working with PHP, this whole thing becomes even easier. The mysql module contains a great function that lets you get the id of the last inserted row:
$last_inserted_row = mysql_insert_id($dblink)
Here, $dblink is a database connection identifier variable (the one returned by mysql_connect statement). The returned key is found based on your unique connection, you can safely assume you are getting the correct value, even in a multi-user server environment. Other users, connecting to the db at the same time will get last unique id’s that match their connection.
[tags]php, mysql, database, primary key, foreign key, sql, insert, last inserted id[/tags]