When dealing with forms, you soon realize that whenever you perform an sql insert, it is usually followed by some method of unique id retrieval. Once you have a handle on the primary key, you can procede to update, delete, or modify in any way you see fit. The question is: How do you find the primary key of the last record inserted? Here are a few of your options:

  • @@Identity works well for those of you using Sql Server. An interesting point to note is that the key returned is that of the open database connection. If you were to do an insert, close the connection, reopen the connection, then check the identity you would receive a null. Additional info can be found at the bottom of this link, and here is a great write up as well.

  • mysql_insert_id() is a PHP Function that works with MySQL. It also seems that this function is session based, so you do not need to worry about overlapping ID’s in a high traffic site.

  • last_insert_id is for those of us using MySQL, but remain true to ASP.

  • The last option is a seed table. Create a table in your database with 3 columns: seedID, tableName, seedValue. This table will manage primary keys for any other table in your database. Before every insert, query this table to find the next available primary key. During each insert, make sure you increment this table so it is accurate for the next insert. I could not find a good tutorial on the subject, so I guess I’ll do a write up on it soon.

HTML Form Builder
Ryan Campbell

The Elusive Primary Key by Ryan Campbell

This entry was posted 5 years ago and was filed under Notebooks.
Comments are currently closed.


  1. Brian Benzinger · 5 years ago

    Wow, how I could have used, mysql_insert_id(), for work the other day. When I inserted a row and had to insert another record in a different table with the primary key from the last (sorry if that sounds confusing), I had to pull up the record just inserted to get the value of the key and then add it. Well, now I know. This will make things easier ;)

    Thanks Ryan.

  2. Kevin Hale · 5 years ago

    Did you just write “but remain true to ASP?” I’ll cut you.

  3. Ryan Campbell · 5 years ago

    A – A S – Superior P – Programming Language

    What’s that spell? ASP!!

  4. Brian Benzinger · 5 years ago

    “Did you just write “but remain true to ASP?” I’ll cut you.”

    Haha. Here we go with a PHP Vs. ASP discussion. Personally, PHP is my favorite language. But, I cannot really say because I haven’t touched ASP in about two years. I should get back into it. I do hear good things, but there are of course bad things too. No language seems to be perfect.

  5. Kevin Hale · 5 years ago

    Except PHP.

  6. Brian Benzinger · 5 years ago

    Agreed, Kevin


  7. Ryan Campbell · 5 years ago

    I’m gonna be fighting a one man battle around here :(