why use auto increment?

osheriff

Hi guys, and gals. What reason would you use an auto increment field if the table has another field that can be used as the primary key.

Thanks

Anon

having a primar key as a unique integer values in my limited experience is a bit better then indexing a string. Its also smaller which can be a plus when you're doing multi-table relationships.

There's also that its fast and puts the burden of assigning a unique ID on the DB server and not the application server.

amcgrath

not to mention you can be assured you'll have a truly unique value....

Anon

not to mention... ahhh... nevermind. just do it.

osheriff

thanks a lot for the help guys....I'm serious

[deleted]

While using an auto incremented integer as the primary key is a good idea for most cases, there are times when I find a string to be the best solution.
For example, tables that store users information:
uname pword first_name last_name address etc.

The user name has to be unique in the first place and it seems more logical to use uname in select statements and cookies so why not make it the primary key?

osheriff

an alternative, nice idea mark. you have just explained what my initial senario was.

sfullman

1) Faster indexing
2) It's a native function of the database and doesn't add much processing time
3) Privacy: using someone's email, for example is fine, but if you try to set a cookie with the email, MSIE's new system can recognize your'e setting "personally recognizable information" and may block that cookie entirely.
4) Assuming you don't delete records but only make them inactive, you have a great handy-dandy counter of your records.

Only drawback to them is you don't know what the next record number is going to be unless you (professionally) lock the DB, get the highest index, submit the record and unloc the DB. You'll find lots of people using this dilemma. If there's anyone out there who knows how to insert a record and get back the auto-increment value in one shot, I'D LOVE TO KNOW.

Sam Fullman
Compass Point Media

Anon

i would always use an autoincrement field in that table, and make it the user id that can be primary in other tables. INTEGER is better than STRING

Anon

uh.... mysql_insert_id();

EVERY database has such a method on a per thread level. without it, there could be no guarantee of data integrity

Anon

I'm in the middle of just such an scenario. However, I still found an autoincrement value to be advantageous.

If the user wants to have the site "remember their login", presumably via a cookie, you don't want the userid or password to be a public value. Since my tables were all already built to use a unique id, I used that as the key that's stored in the cookie. User's can't log on with it, and thus it doesn't compromise security.

I also store an md5 hash of a couple of the user's values in the cookie. When I go to authenticate the cookie, I simply pull the record for the unique id up, build a hash from its values, and then compare it to the hash stored in the cookie. if they match, the user is allowed in.

In a nutshell, having such a value rarely hurts and often can benefit. I know a couple DBA's that insist that every table has such a value. Just on the off chance they are ever needed, they're already there.

[deleted]

"so why not make it the primary key?"

because each and every query would require a string-comparison to identify the record, which is a lot slower than comparing two numbers.

Then there's case sensitivity. Some datatypes are case sensitive, others are not. You'll want the primary key to be truely unique, so you must do some kind of case-shift to make sure that you dont get 'John' and 'john'.

But most importantly, you don't want to use a name for a primary key, because data like a name can change, think of typos, marriage, and plain old 'I want to be called Jane, not John'. Unless your database supports cascading, editing a primary key will drop you in serious sh*t.

auto_increment numbers don't have a case, they don't change, they are uniquely generated every time, they're perfect.

[deleted]

"Only drawback to them is you don't know what the next record number is going to be unless you (professionally) lock the DB, get the highest index, submit the record and unloc the DB"

Actualy, that's just MySQL's way of f*cking things up pretty badly because they don't like to use standards.

Real databases use 'sequences', which you can query just like a table;

SELECT nexval('seq_name');

and it will increase the number by one, and return it to you, and it will do it multi-user safe. No locking, no mess, just consistent data.

btw, as Michael pointed out, normal procedure for MySQL is to just insert a record and later retreive the number that was generated.

Anon

exactlly my point in reply 3, but i got lazy 🙂