What is the best value to use for id in an ActiveRecord pattern (PHP).

auto-increment is out of the question. I would like to generate an id value that I can use to create a new record, but will be unique.

random values have a chance of creating collision.

querying to get the last id used, and then incrementing that value has the chance that another record will be created at approximately the same time with the same id value.

what do most use in this situation? what is the best thing to use for this situation?

  • Why is auto-increment out of the question? Apr 27, 2011 at 0:47
  • @Demian Brecht because you cannot accurately get the last insert id for use in a subsequent query. there will always be a chance that another insert has been made between when you insert a record and retrive the last insert id. also, this option is only available on some rdbms. Apr 27, 2011 at 1:02
  • actually, you can accurately get the last insert id using postgres (INSERT ... RETURNING id postgresql.org/docs/8.2/static/sql-insert.html). not sure if that's applicable to you though, but it sounds like you may not have a db solution decided on yet... Apr 27, 2011 at 1:54
  • @Demian Brecht yes, I have seen examples of that solution, and I like it very much, but I need the code to be able to work with other rdbms. Apr 27, 2011 at 2:19

1 Answer 1


All RDBMSes* that provide an auto-increment function give you the most recent ID on the same connection so even if there's multiple records being inserted at the same time, you're not going to get one that was created by another connection.

I find it beneficial, actually, to use the auto-increment fields because you can know in your code whether you're doing an "insert" or an "update" just by looking at the identifier: zero and it's a new record, non-zero and it's an update of an existing record.

* That I am aware of. I know this is true for MySQL and MS SQL Server at least.

  • out of PDO documentation for lastInsertId() " Note: This method may not return a meaningful or consistent result across different PDO drivers, because the underlying database may not even support the notion of auto-increment fields or sequences. " Apr 27, 2011 at 3:33
  • @dqhendricks: That may be true for some of the more "esoteric" databases (for example, Firebird doesn't have auto-increment support at all... last time I checked anyway) but for the mainstream ones you'd actually use for web development, it's not going to be an issue. Apr 27, 2011 at 3:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.