PDA

View Full Version : smart ID field

lejon
10-20-2006, 05:21 AM
is there a way i can get the id to auto-increment to the next available value? i've already set it to bigint, but what about 5 years down the road? lol. i never wanna have to come back to this site ever, and with a smart id i wouldn't have to.
guelphdad
10-20-2006, 10:27 AM
make the field auto increment. This article (http://guelphdad.wefixtech.co.uk/sqlhelp/lastinsertid.shtml) should give you an idea of how to implement it over more than one table with your data.

note that even with INT you most likely will never run out of ids in your lifetime.
lejon
10-20-2006, 10:29 AM
i mean smart id increment. rows get deleted consistently and added. i don't want to have a gap between id's.
guelphdad
10-20-2006, 10:29 AM
read the article. there is no reason to worry about having gaps in your sequence of numbers.
lejon
10-20-2006, 10:41 AM
yah i didn't think there was an easy way...i wrote an amfphp cms, and it flushes on screen rows to insert new ones in its place. the cms sometimes could have up to 20 records, so that is 20 records flushed and 20 new inserted. i wanted to have it this way instead of doing a check db update or insert type thing.
guelphdad
10-20-2006, 02:32 PM
This is the key piece of information on "gaps" in auto increment sequences of numbers, from the article I've linked:


The second, and more important thing, your application should not rely on there being no gaps in the sequence of numbers. your users should not know anything about the auto increment column, nor should you display it to them. The only reason you should use an auto increment column is when there are no other obvious candidate columns for your primary key. If you have a table using Social Insurance Numbers for instance, those should be your primary key. when all other columns in the table may have duplicates, then and only then should you consider creating a column as your primary key. At that point, the column is only so you don't have duplicate rows, and then you show the users the rest of your columns and not the primary key.


If you have to worry about there not being gaps, then you should not use an auto increment field.