Should Primary Keys be self-incrementing?

[kuau]

It seems that most things I have read about mySQL recommend having a PRIMARY KEY that is an autonumber column in most tables (?).

I have a table in which the invoice number is unique and I want to make it my primary key and not have an autonumber column. Is there any downside to ditching the autonumber column? Thanks.

[traq]

AUTOINCREMENT has the advantage of always being a unique value without making you figure out how to generate/assign it. If that's not a problem for you, then don't worry about it. Use your invoice number.

[kuau]

Thanks for putting my mind at ease. Is there any difference in terms of speed or priority or anything between a primary key field and a regular field defined as a unique index? These are things I have always wondered about.

[traq]

yes, but I'm not the one to ask about those distinctions :D

mysql uses keys (primary, unique, etc.) to create indices, which help with efficiency when searching the table, but I don't know specifically how that works.

[djr33]

Generally, unless I'm sure I won't need it, I always use a primary key auto-incremented. It guarantees that there will be a unique value for each entry (even if the rest of the information is not unique).

There's no reason you need to use that as part of your system though. You can use another column (such as your invoice number), and the other additionally.

If you have another value (invoice number?) that is also guaranteed to be unique, then there's no reason not to use that.