How can I get the Primary Key id of a file I just INSERTED?

Question

Earlier today I asked this question which arose from A- My poor planning and B- My complete disregard for the practice of normalizing databases. I spent the last 8 hours reading about normalizing databases and the finer points of JOIN and worked my way through the SQLZoo.com tutorials.

I am enlightened. I understand the purpose of database normalization and how it can suit me. Except that I'm not entirely sure how to execute that vision from a procedural standpoint.

Here's my old vision: 1 table called "files" that held, let's say, a file id and a file url and appropos grade levels for that file.

New vision!: 1 table for "files", 1 table for "grades", and a junction table to mediate.

But that's not my problem. This is a really basic Q that I'm sure has an obvious answer- When I create a record in "files", it gets assigned the incremented primary key automatically (file_id). However, from now on I'm going to need to write that file_id to the other tables as well. Because I don't assign that id manually, how do I know what it is?

If I upload text.doc and it gets file_id 123, how do I know it got 123 in order to write it to "grades" and the junction table? I can't do a max(file_id) because if you have concurrent users, you might nab a different id. I just don't know how to get the file_id value without having manually assigned it.

Answer 1

You may want to use LAST_INSERT_ID() as in the following example:

START TRANSACTION;
INSERT INTO files (file_id, url) VALUES (NULL, 'text.doc');
INSERT INTO grades (file_id, grade) VALUES (LAST_INSERT_ID(), 'some-grade');
COMMIT;

The transaction ensures that the operation remains atomic: This guarantees that either both inserts complete successfully or none at all. This is optional, but it is recommended in order to maintain the integrity of the data.

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis.

It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0).

Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

Source and further reading:

• MySQL Reference: How to Get the Unique ID for the Last Inserted Row
• MySQL Reference: START TRANSACTION, COMMIT, and ROLLBACK Syntax

Answer 2

In PHP to get the automatically generated ID of a MySQL record, use mysqli->insert_id property of your mysqli object.

Answer 3

How are you going to find the entry tomorrow, after your program has forgotten the value of last_insert_id()?

Using a surrogate key is fine, but your table still represents an entity, and you should be able to answer the question: what measurable properties define this particular entity? The set of these properties are the natural key of your table, and even if you use surrogate keys, such a natural key should always exist and you should use it to retrieve information from the table. Use the surrogate key to enforce referential integrity, for indexing purpuses and to make joins easier on the eye. But don't let them escape from the database