MySQL Forums
Forum List  »  Newbie

Re: Can Autoincrement find the next free number ?
Posted by: Guelphdad Lake
Date: February 25, 2010 08:38AM

I have seen the question on gaps in auto increment sequences asked many times. People get hung up on having a neat sequence of values without "gaps". They ask what to do about the gaps.

Short answer, do nothing, leave them as they are.

Long answer: 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.

The exception to this rule is when you use an auto increment column as your primary key in a main table so that you may use the value of that key as a foreign key when inserting related data in your secondary table(s).

Options: ReplyQuote


Subject
Written By
Posted
Re: Can Autoincrement find the next free number ?
February 25, 2010 08:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.