Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
Why is auto-increment out of the question? –  Demian Brecht Apr 27 '11 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. –  dqhendricks Apr 27 '11 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... –  Demian Brecht Apr 27 '11 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. –  dqhendricks Apr 27 '11 at 2:19

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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. " –  dqhendricks Apr 27 '11 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. –  Dean Harding Apr 27 '11 at 3:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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