0

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?

Improve this question
4
  • Why is auto-increment out of the question? Commented 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. Commented 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... Commented 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. Commented Apr 27, 2011 at 2:19

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • 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. " Commented 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. Commented Apr 27, 2011 at 3:36

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.