[thelist] MySQL queries and AUTO_INCREMENT

Brooking, John John.Brooking at sappi.com
Fri Apr 7 09:42:01 CDT 2006


> : I would like the IDs to be continuous because in another part of my
> : page i generate thumbnails of random photos. The randomly generated
ID
> : is used to retrieve the corresponding thumbnail, so if the ID
doesn't
> : exist because of a gap, I have a problem.
>
> ...
> Can I *strongly* suggest that you do not attempt to use the auto-
> increment as your primary key if that's also the field you are
> going to be renumbering for purposes of your random record
> formula.
>
> I suspect there is probably some other way you can get around
> this. I don't really know mySQL well, but I have this page for
> Access/Jet and SQL Server:
> http://www.adopenstatic.com/faq/randomrecord.asp
>
> Neither of the two mechanisms relies on sequential PK values.
> Maybe something similar could be used in your case?

   I agree that using the primary key for this is only going to cause
you headaches. I suggest a separate field, maybe call it SequenceNbr,
and make THAT where you keep your sequential number. Set it to the next
higher value on insert, shift it down on deletes, don't modify the
primary key, and everyone stays happy. I've done a similar thing on some
tables that need to maintain a fixed order to the records, and it works
very well.

   You might ask why you can't just use that as your primary key? Well,
you could, I suppose, as long as you don't make it AUTO_INCREMENT, so
you and the AUTO_INCREMENT feature are not fighting over control of it
(which is what is happening now). However, as far as the MEANING of the
field, you would be using it for two different things. Using a
potentially-changing sequential number to also identify the row is
problematic over time: How do you know that row #12 now has always been
row #12? You don't. That's why a primary key should never change, and
why you should make them separate fields. The primary key identifies the
row for all time, while the sequence number is simply a property of the
row and may change.

   Hope this helps.

- John

-- 


This message may contain information which is private, privileged or confidential and is intended solely for the use of the individual or entity named in the message. If you are not the intended recipient of this message, please notify the sender thereof and destroy / delete the message. Neither the sender nor Sappi Limited (including its subsidiaries and associated companies) shall incur any liability resulting directly or indirectly from accessing any of the attached files which may contain a virus or the like.




More information about the thelist mailing list