mySql auto increment or not

Hi all. I would like to ask some advise about the use of auto increment in mySql. I’m working on quite a big website. On this website there will be a members section and although so far I have always used auto increment for the primary key, I was wondering if someone can tell me the advantages and disadvantages of using auto increment. In other words, should I use it or not?

Thank you in advance

http://stackoverflow.com/questions/1997358/pros-and-cons-of-autoincrement-keys-on-every-table

http://sheeri.com/content/pros-and-cons-descriptive-foreign-keys%3F

The issue here is not auto-increment vs. non-auto-increment, but surrogate key vs. user-recognisable key. If you decide to go for surrogate keys, then you should also go for auto-incrementing keys, as that’s a far simpler - and safer - approach than writing your own key-generating mechanism.

Whether or not to go for surrogate keys depends on various factors. Nobody here can give you any firm advice without knowing more about your data model, the methods used for updating the tables, and other issues. You might want to read up on the subject before making a decision.

Mike

Thank you Rudy. Keep me left with one question. What would you do?

Hi Mike, what do you need to know to give me some more advise on this matter?

As a very rough guide, if you’ve got a table where there is an obvious identifier, such as unique customer number, product code, or something similar, then you might consider using that as your primary key in place of an auto-incrementing integer. However, that will only work if the key is truly unique, if it is always known (you won’t be able to leave a customer number blank if it’s a new customer and his number hasn’t been assigned yet), and if it is guaranteed to be present.

On the other hand, an integer surrogate key is usually more efficient, and will always meet the above requirements. And if you do have an integer surrogate key, then it makes sense to make it auto-incrementing.

The above is just meant to give you a rough idea of the issues. Personally, I nearly always use auto-incrementing integers, and I generally recommend others to do the same. But only someone who’s involved in the design of the database can make a decision on this point.

Mike

That is an answer I can do something with. Thank you so much

on a members table? definitely the auto_increment

members are constantly asking to change their names

Sorry to disagree. First, there’s no “definitely” about it. Every database is different, and only someone familiar with the design can know what’s right in a particular case.

Secondly, just because members can change their name, that’s not itself an argument for using an auto-incrementing key. It’s an argument for not using the member name as a primary key. There might be a membership number or other identifier that would serve as a perfectly valid primary key.

And, finally - as I said before - the question is not whether or not to use auto-incrementing. The real question is whether to use a surrogate key. The auto-incrementing feature is merely a convenient way of generating an integer key.

I agree that an auto-incrementing surrogate integer key is usually the best way to do. But you need to consider it in context, and you should never rule out other possibilities.

Mike

sorry to disagree, but there ~is~ definitely a “definitely” about how i feel

he asked for my opinion, and it’s pretty definite

i respect your opinion and your valuable contributions, but to quote your own words, “only someone familiar with the design can know what’s right in a particular case” and in the particular case of ~my~ database design, i’m definitely going to use the auto_increment for the members table

:slight_smile:

That’s fine. I don’t disagree with that. But my remarks were meant to apply to ~your~ database design. I was just trying to clarify things for the original questioner.

Mike

that’s fine

but you ~did~ quote me directly, and specifically took issue with my opinion about how ~i~ would do it

:slight_smile: