Re: Adding Records & Capture The New Record ID

On Mon, Feb 9, 2009 at 10:27 AM, tedd <tedd.sperling@gmail.com> wrote: > At 9:54 AM -0500 2/9/09, Andrew Ballard wrote: >> >> You are right, but the point is that if you intend something to be >> unique then you should declare it as such in the database. > > We didn't discuss that. > > You are saying that if I checked my database for a email address and find > it's not there [1] and then create a record for it [2] that sometime after > [1] and before [2] someone else could create a record with the exact same > email address -- is that it? Yes. For small sites with little traffice this is probably unlikely, but it's possible. > If so, then there would be two records with the same email address -- that > would be a problem regardless of if I was asking for the email address or > the record's ID. On one hand I would get the record that someone else just > created and on the other hand, I would get the record I just created. In > either case, it would be a mistake -- I see. > > Also, if the field was declared as unique, then my script to create my > record would fail. Correct, but I believe it does so silently, meaning you'll have to check for mysql_error() to make sure no errors were returned and/or mysql_affected_rows() === 1 to make sure that exactly one record was inserted. > So the only way to do this properly is to declare the field unique, lock > down the database between [1] and [2] and check for errors afterwards -- > interesting. Close, but not exactly. If you declare a UNIQUE INDEX on the column, you don't need to lock down the database between 1 and 2. The DBMS should handle that for you internally. You just run your insert process as normal, except that you add a check afterward to make sure that it was successful (which you should be doing anyway). Andrew